Delete Hyperlinks without removing formatting

[Pauljj]

If I use the following code to delete hyperlinks it also removes the formatting, can the code be changed to only remove the link and nothing else ?

For Each ws in ThisWorkbook.Worksheets
ws.Hyperlinks.Delete
Next ws

 

[erik.van.geit]

Hi,

You will need to loop through the hyperlinks and handle their "parents". If the parent is not a range (could be an object, picture, ...), the code must skip to the next one.

try this

Option Explicit

Sub remove_hyperlinks_keep_font()
'Erik Van Geit
'070809

Dim ws As Worksheet
Dim h As Hyperlink
Dim a As String

On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
        For Each h In ws.Hyperlinks
            With h.Parent
            a = .Address
                If Err Then
                Err.Clear
                Else
                'more code could retrieve the font before deleting the hyperlink
                'this code assumes standard settings are applied
                h.Delete
                .Font.ColorIndex = 5
                .Font.Underline = xlUnderlineStyleSingle
                End If
            End With
        Next h
    Next ws

End Sub

kind regards,
Erik