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
 theHyperlink.Delete
Next
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
      theHyperlink.Delete
    End If
 Next
End Sub

Here's a screen shot of our example file:

Remove-Hyperlinks

 

Download spreadsheet to practise how to Remove Hyperlinks.

 

 

Training Video on how to remove Hyperlinks:

AttachmentSize
Remove-Hyperlinks.xls52 KB