Programmatically delete hyperlinks without applying hyperlink style

2
votes

I have a set of hyperlinks on a worksheet. The text in each link is Arial 8pt, and is centered. I know that I can remove the links as follows:

Range("a1:a10").Hyperlinks.Delete

However, I'm having a problem with the font changing on the links when I delete them; when I run this delete operation, the text in each cell gets set to Arial 10pt and becomes left-justified.

I checked the Hyperlink style for this workbook and it's set to Arial 10pt. I'm not sure why the hyperlinks are 8pt when the style indicates that they should be 10pt.

Anyways, I'm trying to figure out how to preserve the existing formatting of a cell after I delete the hyperlinks, so that it stays 8pt and centered. I tried this:

Sub removeHyperlinks()

Dim rng As Range
Dim subrng As Range


Set rng = ThisWorkbook.Sheets("myData").Range("a1:a10")

For Each subrng In rng
    subrng.Copy
    subrng.Hyperlinks.Delete
    subrng.PasteSpecial Paste:=xlPasteFormats
Next


End Sub

But the hyperlinked cells nonetheless ended up as Arial 10pt, left-justified. How can I keep the formatting from changing?

1
vbaexcel
I know you can 'Undo' when a hyperlink formats itself, and it will take a step back to leave the typing. Could you try adding an undo? stackoverflow.com/questions/339228/… or stackoverflow.com/questions/7004754/… – Grant
I've never done this so I'm just throwing that out as a possibility FYI. – Grant
I just tried adding Application.Undo after the subrng.Hyperlinks.Delete line, and got a Method 'Undo' of object '_Application' failed error. – sigil

1 Answers

1
votes

Here is one way to work around it:

Dim fntsize As Integer

For Each subrng In rng
    fntsize = subrng.Font.Size
    subrng.Hyperlinks.Delete
    subrng.Font.Size = fntsize
Next