Named Ranges with Blank Names and Multiple Names

[tmurj]

I have a WB that contains several worksheets.

I am using named ranges in several formulas in order to make the data dynamic. It appears there may be some corruption since I now when I pull up the Name Tool (using Insert, Name, Define) the first few lines have a blank name and the "Refers to:" shows =#REF!$A$1:$L$53

In addition, the same name apears multiple times all refering to the same range.

I have the following VBA code that will list eah of the occurences in a sheet titled WSInfo:

Sub ListAllNamedRanges()
    Dim Nms As Names, Nm As name, NameName As String
    Dim Wks As Worksheet, r As Integer
    'ActiveWorkbook.Names
    Set Nms = ActiveWorkbook.Names
    Set Wks = Worksheets("WSInfo")
    On Error Resume Next
    'For r = 1 To Nms.Count
    r = 1
    For Each Nm In Nms
        NameName = Nm.name
        Debug.Print NameName
        'If InStr(1, NameName, "!_FilterDatabase") = 0 And InStr(1, NameName, "'!Print_") = 0 Then
            Wks.Cells(r + 1, 1) = r
            Wks.Cells(r + 1, 2).Value = "'" & Nm.name 'Nms(r).name
            Wks.Cells(r + 1, 3).Value = Nm.RefersToRange.Address 'Nms(r).RefersToRange.Address
            Wks.Cells(r + 1, 4).Value = "'" & Nm.RefersTo 'Nms(r).RefersTo
            If InStr(1, Nm.RefersToRange.Address, ":") = 0 Then 'Nms(r).RefersToRange.Address, ":") = 0 Then
                Wks.Cells(r + 1, 5).Value = Nm.Value 'Nms(r).Value
            End If
            r = r + 1
        'End If
    Next
    Set Wks = Nothing
    Set Nms = Nothing
End Sub

It produces output shown below: (The first 6 lines are the Blank Names)
Named Ranges
1 =#REF!$A$1:$L$53
2 =#REF!$A$1:$L$53
3 =#REF!$A$1:$L$53
4 =#REF!$A$1:$L$53
5 =#REF!$A$1:$L$53
6 =#REF!$A$1:$L$53
7 AdjusterField!_FilterDatabase $A$16:$K$38 =AdjusterField!$A$16:$K$38
8 AdjusterSummary!_FilterDatabase $A$16:$S$38 =AdjusterSummary!$A$16:$S$38
9 AdjusterWaiver!_FilterDatabase $A$16:$K$38 =AdjusterWaiver!$A$16:$K$38
10 McAllister!_FilterDatabase $A$1:$V$63 =McAllister!$A$1:$V$63
11 WSInfo!_FilterDatabase $L$1:$Q$66 =WSInfo!$L$1:$Q$66
12 AdjTrendCnt =AdjusterField!#REF!
13 AdjTrendCnt =AdjusterWaiver!#REF!
14 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
15 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
16 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
17 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
18 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
19 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
20 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
21 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
22 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
23 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
24 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
25 AdjTrendCnt $N$5:$N$42 =AdjusterSummary!$N$5:$N$42
26 ChtField $C$179:$J$190 =UnitSummary!$C$179:$J$190
27 ChtField $C$179:$J$190 =UnitSummary!$C$179:$J$190
28 ChtField $C$179:$J$190 =UnitSummary!$C$179:$J$190
29 ChtField $C$179:$J$190 =UnitSummary!$C$179:$J$190
30 ChtField $C$179:$J$190 =UnitSummary!$C$179:$J$190

Sub DeleteAllNamedRanges()
    Dim Nms As Excel.Names, Nm As Excel.name, NameName As String, r As Integer
    Set Nms = ActiveWorkbook.Names
    r = 1
    For Each Nm In Nms
        Debug.Print Nm.name
        If Nm.name = "" Then
            Nm.Delete
        End If
            r = r + 1
    Next
    Set Nms = Nothing
End Sub

Running the above code has no effect on the named ranges.

Anyone dealt with this sort of issue before?

 

[tmurj]

I am also unable to delete any of the mutliple named ranges using the built in dialog box (from menu Insert, Name, Define). I can find the name in the list and highlight it and then click delete. The list refreshes, but the name does not get removed.