Named Ranges with Blank Names and Multiple Names

tmurj

New Member
Joined
Jun 21, 2007
Messages
23
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:

Code:
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


Code:
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?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

tmurj

New Member
Joined
Jun 21, 2007
Messages
23
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.
 

Forum statistics

Threads
1,144,374
Messages
5,723,989
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top