Hyperlinks.Delete erases cell formatting

[Glen K]

Using the Hyperlinks.Delete method wipes out all of the formatting in
the affected cells. I'm sure I could write a macro that reformats the
cells,but the workbooks involved are quite large and I need to do this as part
of a mulit-user web application. Considering how slow Excel is at cell
formatting, I don't think this is a good idea.

I was wondering if there is a fast way to fix this or if newer versions
f Excel have this bug fixed (I am using Excel 2000 currently).

 

[Jim Cone]

Glen,

The following works almost instantly, as all operations are on the entire range...
'---------------------------------------------------------
Sub CleanItUp()
'Removes Hyperlink objects from designated range.
'Jim Cone - December 16, 2004
Dim varCellValue As Variant
Dim rngHyperArea As Excel.Range

Set rngHyperArea = Range("B8:G200")

varCellValue = rngHyperArea.Value
rngHyperArea.ClearContents
rngHyperArea.Value = varCellValue
rngHyperArea.Font.Underline = xlUnderlineStyleNone
rngHyperArea.Font.ColorIndex = xlColorIndexAutomatic

Set rngHyperArea = Nothing
End Sub
'------------------------------------------------

Jim Cone
San Francisco, USA

 

[Glen K]

That seems to do the trick, thanks.

Glen,

The following works almost instantly, as all operations are on the entire range...
'---------------------------------------------------------
Sub CleanItUp()
'Removes Hyperlink objects from designated range.
'Jim Cone - December 16, 2004
Dim varCellValue As Variant
Dim rngHyperArea As Excel.Range

Set rngHyperArea = Range("B8:G200")

varCellValue = rngHyperArea.Value
rngHyperArea.ClearContents
rngHyperArea.Value = varCellValue
rngHyperArea.Font.Underline = xlUnderlineStyleNone
rngHyperArea.Font.ColorIndex = xlColorIndexAutomatic

Set rngHyperArea = Nothing
End Sub
'------------------------------------------------

Jim Cone
San Francisco, USA