Deleting Broken Defined Name

See if this helps locate the problem Named Ranges.

Option Explicit

Sub ListNamedRanges()

Dim nm As Name, vArray, i As Long

' pick somewhere to output the details
Dim rOutput As Range
Set rOutput = Range("F1")   ' adjust as required

'Debug.Print ThisWorkbook.Names.Count
If ThisWorkbook.Names.Count = 0 Then Exit Sub

' resize the array to store the names and references
ReDim vArray(1 To ThisWorkbook.Names.Count, 1 To 4)

' loop through the names in the workbook
For Each nm In ThisWorkbook.Names
    'Debug.Print nm.Name, nm.RefersTo, nm.Value
    i = i + 1
    vArray(i, 1) = nm.Name
    vArray(i, 2) = nm.RefersTo
    vArray(i, 3) = nm.Value
    If InStr(nm.Value, "#REF!") > 0 Then
        vArray(i, 4) = "check"
    End If
Next 'i

' output the array of Name details
With rOutput
    .Resize(, 4).Value = Array("Name", "Refers to", "Value", "Check")
    With .Offset(1).Resize(ThisWorkbook.Names.Count, 4)
        .NumberFormat = "@"
        .Value = vArray
    End With
End With

End Sub

Sample output

HTML Code: 

You may need to loop through it by names count.

    Dim n As Name, x As Long
  
    For x = ActiveWorkbook.Names.Count To 1 Step -1
        Set n = ActiveWorkbook.Names(x)
        If InStr(n, "#REF!") > 0 Then
            n.Delete
        End If
    Next x

Did my answer in your previous thread help at all?

Administrative Note:

Welcome to the forum.

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.

I have merged both threads to prevent confusion.


DO NOT duplicate threads in future.