Delete range names with #REF errors

A macro to the rescue

Here is an example of a simple macro that solves a problem in Excel 2003 and earlier versions.

Range names can be corrupted if a cell that they refer to gets deleted. This doesn’t mean that the cell value gets deleted, but the cell itself is removed from the sheet.

If a range name refers to cell A1 and you delete the whole of column A then the name will display the #REF error because the cell it referred to no longer exists.

Excel 2007 and 2010 both allow you to delete multiple names at once. So you can open the Name Manager (Ctrl + F3) and then sort by Value by clicking the Value heading and then select all the names with a #REF value and click Delete. It’s a fairly easy process.

Not so in Excel 2003. It’s a boring, time consuming task. You can’t sort names by value and you can only delete names one at a time.

The macro below will remove all range names that have the #REF error in the blink of an eye. The macro checks if you want to continue before running. It works in all versions.

Sub DeleteAllNamesWithREFError()
 
'deletes all the names in the active workbook 
 
'with a #REF error- confirms before running
 
Dim N As Name
 
If MsgBox("Are you sure?", vbYesNo + vbDefaultButton2, "Confirm macro") = vbNo Then Exit Sub
 
For Each N In ActiveWorkbook.Names
 
If InStr(N.Value, "#REF") Then N.Delete
 
Next N
 
End Sub

The attached file has the macro and some names with errors. Clicking the button will remove the names with errors.

Names With Errors

Click here to see how you can run a Macro off a Quick Access Toolbar icon.

Related Posts

Posted on
Categories: Excel Blog, Macros, Tips & Tricks

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

10 thoughts on “Delete range names with #REF errors”

  1. I want to remove/delete #REF from Line Charts. Could you pls provide me the solution.
    Thanks,
    Bhavik

    Reply

        • Hi Bhavik

          Can’t see how to do it. You can’t access the names of the legend entries via vba.

          Sorry

          Neale

          Reply

          • Hi Neale
            The scenario is as follows :
            In sheet there is almost 10-15 charts. Each chart refer different data tables on the same sheet. When I delete a row from data table, there is a REF# in Legend of that chart. So I nee to remove this REF# entry from Legend of that chart.
            Any workaround using macro ?
            Regards,
            Bhavik

          • Instead of deleting the row can’t you hide it? Hidden rows don’t display on the chart.

          • Hi Neale,
            Totally Agree.
            We have implemented this solution but not accepted. In Row Headings, it is very clearly visible lets say after 10th Row there is 12th Row & 11th Row is hidden.
            Thanks
            Bhavik

          • You can use grouping to show a row is hidden.
            Select row 11 and hold Shift + Alt and press the right arrow – this adds an icon and makes it easy to hide and unhide,

  2. I was just wondering, if checking for “#REF” in the value property using the instr function is the only way to check if a name has errors in it.

    Is there a method where the instr function is not used?

    Reply

    • You could go thru the value character by character but Instr is the most efficient way to do it.

      Reply