Posts tagged shortcuts

The other day I retweeted a handy tip from Tom Urtis:

It can be frustrating if you have a large number of hyperlinks in an Excel spreadsheet which you want to remove as usually you’d have to go through each one individually to delete them all. This solution is a simple piece of VBA to go do them in bulk and is a great example of a quick piece of code as well as giving a few examples of handy shortcuts.

Make sure that you’ve got the spreadsheet open, with the active sheet being the one with the links on, then follow the steps above in Tom’s tweet. Broken down the shortcuts do the following:

_____________________________________________________________________________________

ALT + F11 Opens the VBA (Visual Basic for Applications) Editor.

CTRL + G Displays the Immediate Window, a place used often for debugging macros, but in this case a handy way of executing a single code immediately.

Type: ActiveSheet.Hyperlinks.Delete Does exactly what it says – checks the active sheet for hyperlinks and then deletes them

Enter Executes the code

Alt+Q Closes the VBA editor

_____________________________________________________________________________________

You should be left with a sheet with no hyperlinks, just their values – be warned if you want to do this on more than one worksheet in the same Excel workbook you’ll need to repeat it and also that you can’t undo this using the standard CTRL + Z. Perhaps a good opportunity  to use Save As?