Delete Empty or 'Broken' Named Ranges (#REF!)

Add to Favorites

This macro will delete named ranges which refer to empty or #REF! ranges. This means that if a named range has been 'broken' so that it now says "#REF!" this named range will be deleted from the workbook. This can often happen when deleting a tab with a named range or when importing data from other excel worksheets or workbooks.

Note: When you run this macro, it will delete all emtpy or 'broken' named ranges in the entire excel workbook. It will not ask you if you want specific named ranges deleted. Therefore, make sure you do not accidentally delete something which you really want to be there.

Where to install the macro:  Module

Delete Empty or 'Broken' Named Ranges (#REF!)

Sub Delete_Empty_Named_References()

Dim nName As Name

For Each nName In Names

If InStr(1, nName.RefersTo, "#REF!") > 0 Then

  nName.Delete

 End If

Next nName
    End Sub





Similar Content on TeachExcel
Delete Multiple Named Ranges Quickly
Macro: This macro will allow you to delete multiple named ranges very quickly. When you run this ...
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
Macro: List all of the named ranges in a workbook in Excel and the corresponding values store...
Delete Empty Columns
Macro: This macro will delete columns which are completely empty. This means that if there is no ...
Check if Cell is Empty or Not in Excel
Tutorial: How to check if a cell is empty or is not empty in Excel; this tutorial shows you a couple...
Dynamic Named Range in Excel
Tutorial: How to create a named range that expands automatically when a new value is added to the r...
Error Values in Excel - Full Explanation
Tutorial: Here, I'll teach you what the errors in Excel mean.  There are many errors that you can ...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Delete Multiple Named Ranges Quickly
Macro: This macro will allow you to delete multiple named ranges very quickly. When you run this ...
List All Named Ranges in Excel - Displays the Name and Value for Every Named Range Within the Active Workbook in Excel
Macro: List all of the named ranges in a workbook in Excel and the corresponding values store...
Delete Empty Columns
Macro: This macro will delete columns which are completely empty. This means that if there is no ...
Excel Forum