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.Hyperlinks.Delete
mySelectionRange.NumberFormat = "@"
mySelectionRange.Value = strMyString
Next
On Error GoTo 0
Application.ScreenUpdating = True

End Sub


Until next time...Rich

Comments

Popular Posts