Remove Hyperlinks Preserve Cell Format

Question

I have some cells in a worksheet that contain Inserted hyperlinks. I want to remove the hyperlinks and leave the "friendly name" in the cell. I can do this with:

Sub dural()
   Dim h As Hyperlink

   For Each h In ActiveSheet.Hyperlinks
      h.Delete
   Next h
End Sub

This little Sub works. However if I start with:

and run the macro, I get:

The format of the cell has been ruined! Is there anyway to remove the hyperlink and leave the formatting alone??

EDIT#1:

examining hyperlink properties, I got this to work:

Sub dural2()
   Dim h As Hyperlink, addy As String, z As String

   For Each h In ActiveSheet.Hyperlinks
     addy = h.Range.Address
     z = h.Parent
     Range(addy).ClearContents
     Range(addy).Value = z
   Next h
End Sub

Mark Fitzgerald Mark Fitzgerald · Accepted Answer · 2015-12-05T01:49:46

The Hyperlink object has certain properties which, through trial and error I established that, for a simple hyperlink to another cell on the same sheet:

h.Address = ""
h.Range = "CNN" (actually returns a Range but as the default property is .Value it evaluates to "CNN")
h.SubAddress = "Sheet1!C1"

As .SubAddress contains:

the location within the document associated with the hyperlink

I changed your code to:

Sub dural()
   Dim h As Hyperlink

   For Each h In ActiveSheet.Hyperlinks
      h.SubAddress = ""
   Next h
End Sub

and found that the hyperlink no longer works but your cell formatting is preserved.

Remove Hyperlinks Preserve Cell Format

1 Answers