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.
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