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.

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