An Excel Nugget: removing the hyperlinks in a range

There's probably a better way of doing this, but I wrote a simple VBA macro to strip out the hyperlinks and show the contents of the link and write the url to the cell. Please note though that the text originally display will vanish if you use this!

Sub LoopThruClearHyperlinks()
Dim strMyString As String
Dim mySelectionRange As Range
Application.ScreenUpdating = False
On Error Resume Next
For Each mySelectionRange In _
Intersect(Selection, ActiveSheet.UsedRange)
strMyString = mySelectionRange.Hyperlinks(1).Address
mySelectionRange.NumberFormat = "@"
mySelectionRange.Value = strMyString
On Error GoTo 0
Application.ScreenUpdating = True

End Sub

Until next time...Rich


Popular Posts