7. VBA tips - remove Hyperlinks

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
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:
Download spreadsheet to practise how to Remove Hyperlinks.
Training Video on how to remove Hyperlinks:
Attachment | Size |
---|---|
Remove-Hyperlinks.xls | 52 KB |
»
- Nick's blog
- 18185 reads
Recent comments
5 years 50 weeks ago
6 years 36 weeks ago
6 years 47 weeks ago
6 years 50 weeks ago
6 years 51 weeks ago
7 years 5 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago