7. VBA tips - remove Hyperlinks

Nick's picture

Hyperlinks can be like mosquitoes

- Sometimes you can't see them and they get you when you're least expecting.

The reason is that even if you clear the contents of a cell, the hyperlink can remain. Time to blast them with a bit of VBA. It's easily done... Hyperlinks are stored on a sheet by sheet basis, so all you have to do is to loop through the hyperlinks collection of the active sheet and delete them.

Here's how:

For Each theHyperlink In ActiveSheet.Hyperlinks
Easy. Now if you want to add back the hyperlinks that you actually want, just click on the cells, and press F2 then Enter. Or you can just delete the hyperlinks for empty cells:
Sub RemoveHyperlinks()
 For Each theHyperlink In ActiveSheet.Hyperlinks
    If theHyperlink.Range.Value = "" Then
    End If
End Sub

Here's a screen shot of our example file:



Download spreadsheet to practise how to Remove Hyperlinks.



Training Video on how to remove Hyperlinks:

Remove-Hyperlinks.xls52 KB