You are here:

Excel/Retain Format AND Formulas removing hyperlink


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 ( 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:

Sub ClearHyp()
   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
         Unused.PasteSpecial xlPasteFormats
         thing.PasteSpecial xlPasteFormats
       End If
End Sub
About Excel
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


All Answers

Answers by Expert:

Ask Experts


Bob Umlas


I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at


Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Excellence, The Expert, Microsoft

BA in math, Hofstra University, 1965

Awards and Honors
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 All rights reserved.