Unwanted effects of Deleting Hyperlinks 1

[PeterMoran]

Hi,

I have this Excel 2K macro:

Sub RemoveHLinks()
'Remove all Hyperlinks in Selection

Application.ScreenUpdating = False
Do Until Selection.Hyperlinks.Count = 0
Selection.Hyperlinks(1).Delete
Loop
Application.ScreenUpdating = True
End Sub

It removes the hyperlinks fine, but also removes any formatting on the relevant cells, such as Bold and Background colour, which normally I do not want to lose.

Is it possible to simply modify the macro so it only deletes the hyperlinks, and leaves the other properties of the cells unchanged?

Thanks in advance.

Peter Moran

 

[fumei]

Make the cell text = ""?

Gerry

 

[PeterMoran]

Hi Gerry,

Thanks for your reply.

I copy a portfolio table from the internet most days, and its headings are hyperlinks.

I have found that hyperlinks take up a lot of space in a spreadsheet, and was wanting to eliminate them as they are not needed.

I want the heading, if possible, to retain its text and formatting, but just lose the link.

Running my macro leaves the cell text, but without any formatting or background colour - like Clear All Formatting!

It does not quite seem to make sense that deleting the hyperlink, removes formatting, but leaves the cell text!

Thanks again,

Peter Moran

 

[xlbo]

Hyperlinks are objects rather than text in a cell so any formatting that has been applied to them will disappear as the object is deleted

To keep the formatting, you will probably need to do something like this (depending on the properties you want to retain)

Sub RemoveHLinks()
    Dim hyper As Hyperlink
    Dim rng As Range
    Dim f, fc, bc, fs
    Application.ScreenUpdating = False
    For Each hyper In Selection.Hyperlinks
    
    Set rng = hyper.Range
    
    f = rng.Font.Name
    
    fc = rng.Font.ColorIndex
    
    bc = rng.Interior.ColorIndex
    
    fs = rng.Font.FontStyle
    
    hyper.Delete
    
    rng.Font.Name = f
    
    rng.Font.ColorIndex = fc
    
    rng.Interior.ColorIndex = bc
    
    rng.Font.FontStyle = fs
    
    Next
    
    Application.ScreenUpdating = True
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PeterMoran]

Hi Geoff,

Thanks for the explanation. I now understand much better what happens.

Will take your advice and amend my code accordingly.

Thanks again.

Peter Moran