Excel/Retain Format AND Formulas removing hyperlink
QUESTION: Hi Bob,
I have recently ran into a problem. One of our timers is creating an excel sheet with multiple (100's) of hyperlinks hidden within the page and it is causing many issues. The issue with just removing the hyperlink is that it is changing the format when doing so. I found code here (http://en.allexperts.com/q/Excel-1059/2010/10/Retain-Format-removing-Hyperklink.
) which takes care of that. The only issue is that now it removes all the formulas and I'm just getting #REF! in its place. I'm not very good with excel/macros but I assume there's a way to get around this. I tried HR.PasteSpecial xlPasteFormulas but that didn't seem to work, any help would be much appreciated!
ANSWER: Set up a cell style which looks just like a hyperlink (blue font, underline). Say that style is ABC. Now remove hyperlinks, then reformat those cells with the ABC style.
---------- FOLLOW-UP ----------
QUESTION: I guess I didn't think this was important, but it also creates about 500 Cell Styles. I cannot delete these or add any new ones, the normal style isn't even available. Also the hyperlinks are hidden, so the only way to know they're there is to scroll over and have it display via popup or to click and it brings you to wherever it's linked too, so I'm not sure if this would work? Also some cells are bold, some with background colors, some both and I want to be sure to keep these styles. Thank you for your help in advance.
This routine will clear the hyperlinks and keep the formatting:
Dim thing As Range, Unused As Range
On Error Resume Next
'find unused cell to use to hold format:
Set Unused = Range("A1").Offset(ActiveSheet.UsedRange.Rows.Count)
For Each thing In Cells.SpecialCells(xlCellTypeConstants)
Set X = thing.Hyperlinks(1)
If Err.Number = 0 Then 'Hyperlink
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here