Delete Empty or ‘Broken’ Named Ranges (#REF!)


Dim nName As Name
For Each nName In Names
    If InStr(1, nName.RefersTo, "#REF!") > 0 Then
        nName.Delete
    End If
Next nName
Sub TheBookWithNoNames()
    Dim nm As Name
    For Each nm In ThisWorkbook.Names
        nm.Delete
    Next nm
End Sub
Sub DeleteNamedRanges()
    Dim MyName As Name
    For Each MyName In Names
        ActiveWorkbook.Names(MyName.Name).Delete
    Next
End Sub

'This is working fine
Sub DeleteRefErrRanges()
    Dim nm As Name
    On Error Resume Next
    For Each nm In ActiveWorkbook.Names
        nm.Delete
    Next
    On Error GoTo 0
End Sub

Sub Delete_All_Names()
    Dim wbBook As Workbook
    Dim nName As Name
    Set wbBook = ActiveWorkbook
    For Each nName In wbBook.Names
        nName.Delete
    Next nName
End Sub

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