Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Windows (http://www.go4expert.com/articles/windows/)
-   -   Deleting all hyperlinks from a Microsoft Excel document (http://www.go4expert.com/articles/deleting-hyperlinks-microsoft-excel-t3196/)

pradeep 28Feb2007 18:08

Deleting all hyperlinks from a Microsoft Excel document
 
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: VB

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: VB

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!


All times are GMT +5.5. The time now is 06:33.