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

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

---