Removing hyperlink without removing the font settings /border sett

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

San - 27 Sep 2007 15:34 GMT

The below code removes hyperlinks
Sub ZapHyperlinks()
Cells.Hyperlinks.Delete
End Sub
However it also removes the formatting(color/border) of the cell .
Any idea on how to protect the format of the cell

Reply to this Message

Tom Ogilvy - 27 Sep 2007 15:58 GMT

Maybe something like this:

Sub ZapHyperlinks()
Set sh = ActiveSheet
ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
Set sh1 = ActiveSheet
sh.Activate
Set r = Selection
Cells.Hyperlinks.Delete
sh1.Cells.Copy
sh.Cells.PasteSpecial xlFormats
r.Select
Application.DisplayAlerts = False
sh1.Delete
Application.DisplayAlerts = True
End Sub

Signature

Regards,
Tom Ogilvy

> The below code removes hyperlinks
> Sub ZapHyperlinks()
> Cells.Hyperlinks.Delete
> End Sub
> However it also removes the formatting(color/border) of the cell .
> Any idea on how to protect the format of the cell

Reply to this Message

Gary''s Student - 27 Sep 2007 16:37 GMT

Another way:

Sub hyper_be_gone()
For Each r In ActiveSheet.UsedRange
If r.Hyperlinks.Count > 0 Then
r.ClearContents
End If
Next
End Sub

Signature

Gary''s Student - gsnu200747

Reply to this Message