CODEWITHSUNDEEP
vbaexcelhyperlink
Eric
Eric

Reputation: 708

VBA deleting Hyperlinks Resets Formatting

I have this vba sub that is designed to replace cell hyperlinks with the HYPERLINK function. However, the currentCell.Hyperlinks.Delete line causes all cell formatting to be reset (font, font size, bolding, underlining, cell borders, alignment, etc.). Is there any way to prevent it from doing this other than manually copying each cell property and then resetting the property after deleting the Hyperlinks? Also, it looks like setting the cell formula also resets the font, but if that is the only property that gets messed up, I can handle it.

Sub Test()

Dim currentCell As Range
Dim directoryBase As String

directoryBase = "\\examplePath\"

For Each currentCell In Worksheets(2).UsedRange.Cells

    If currentCell.Hyperlinks.Count > 0 Then
        currentCell.Formula = "=HYPERLINK("" + directoryBase + currentCell.Hyperlinks(1).Address + "",""ü"")"
        currentCell.Hyperlinks.Delete
        Debug.Print currentCell.Address
    End If


Next currentCell

End Sub

Thanks

Upvotes: 2

Views: 3376

Answers (2)

Symen
Symen

Reputation: 106

I found the solution was to call 

Range.ClearHyperlinks

instead of 

Range.Hyperlinks.Delete

the former clears the hyperlinks and leaves the formatting intact. Reference here: https://msdn.microsoft.com/en-us/library/office/ff194741.aspx

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Here is Matteo's suggestion in code (using a helper cell):

Sub dural()
    Dim r As Range, helper As Range
    Set helper = Range("Z100")
    For Each r In ActiveSheet.UsedRange
        If r.Hyperlinks.Count > 0 Then
            If Intersect(r, helper) Is Nothing Then
                r.Copy helper
                r.Hyperlinks.Delete
                helper.Copy
                r.PasteSpecial (xlPasteFormats)
            End If
        End If
    Next r
End Sub

Just insert your formula maker into the loop after the r.Hyperlinks.Delete

Upvotes: 2

Related Questions