Deleting all hyperlinks from a Microsoft Excel document

Discussion in 'Windows' started by pradeep, Feb 28, 2007.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    1. Same process as a MS Word document. From Excel, fire up the VB editor (ALT+F11).
    2. The Visual Basic interface will open up. From the INSERT menu, click on MODULE to add a module.
    3. A new document opens up. In there, paste the following code:

    Code:
    Sub RemoveHyperLinksGLobally()
      Dim i As Integer
      For i = ActiveSheet.Hyperlinks.Count To 1 Step -1
        ActiveSheet.Hyperlinks(i).Delete
      Next i
    End Sub
    
    4. The only difference from the code for MS Word is highlighted in green. ActiveSheet, instead of ActiveDocument.
    5. Now, from the FILE menu, select "CLOSE AND RETURN TO MICROSOFT EXCEL".
    6. When back into your Excel document, now just press ALT F8 to bring up the MACROS selection window (or you can always do TOOLS —> MACRO —> MACROS) and select the right macro. Bingo.

    However, since we only work with the "ActiveSheet" in the above example, we remove the hyperlinks only from the single active sheet that is currently on the screen. To remove all the hyperlinks from all the worksheets in an Excel document, this code is handy:

    Code:
    Sub RemoveHyperLinksGLoballyFromAllWorksheets()
        Dim i As Integer, wSheet As Worksheet
        For Each wSheet In Worksheets
            For i = wSheet.Hyperlinks.Count To 1 Step -1
              wSheet.Hyperlinks(i).Delete
            Next i
        Next wSheet
    End Sub
    Hopefull, this gives you ideas on how to author other macros too. With slight tweaks in code, they can be used across all Office software. Macros are powerful ways of making your editing fast. Plus, they are only a keystroke away (ALT+F8) whenever you need them!
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice