Steve_Brady
asked on
Remove all hyperlinks from an Excel worksheet without changing cell formatting
Hello,
Some time ago in another thread, expert (chris_bottomley) provided me with a VBA script to remove all hyperlinks from an Excel worksheet. The code is as follows:
Can someone provide a modification of the code so that all hyperlinks are removed but all formatting in the affected cells stays as it was?
Thanks
Some time ago in another thread, expert (chris_bottomley) provided me with a VBA script to remove all hyperlinks from an Excel worksheet. The code is as follows:
Sub noHyper()
Dim lnk As Hyperlink
Dim addr As Range
Dim delText As Boolean
delText = MsgBox("Delete Cell Text as well as link?", vbYesNo, "Hyperlink Delete") = vbYes
For Each lnk In ActiveSheet.Hyperlinks
Set addr = ActiveSheet.Cells(lnk.Range.Row, lnk.Range.Column)
lnk.Delete
If delText Then addr = ""
Next
End Sub
The code is great and does just as intended, i.e. removes every hyperlink in a worksheet in one step. However, I just noticed that it also removes all formatting for the cells which previously had hyperlinks.Can someone provide a modification of the code so that all hyperlinks are removed but all formatting in the affected cells stays as it was?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.