Thursday, May 17, 2007

Excel and its annoying quirks

Excel has this annoying quirk of propagating unwanted hyperlinks in the background of spreadsheets, and in our organization, it gets to the point where it infests every cell of the sheet. But, I did find a fix (sorta). Press Alt+F11 to go into the VB editor in Excel, go to Insert -> Module, and paste this in:

Sub RemoveHyperlinks()

'Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete

End Sub


Save it, return to the worksheet, press Alt+F8 to open the macros menu (Tools -> Macro -> Macros otherwise) and run the Macro of the same name as the subroutine above. This should delete all your hyperlinks. Unfortunately, this will most likely remove all other special formatting in the cells as well. If you have a really large worksheet, it will most likely cause Excel (XP and earlier at least) to crash, so you should really save your work immediately before you do this (or before you add the Macro to the sheet).

No comments: