Possible corruption with Named Ranges

[mcomp72]

I was making a copy of a sheet in my workbook, and the first copy I made worked with no problem. But then I went to make another copy of that same sheet, and a message popped up saying a Named Range already existed and asked if I wanted to rename it. The thing is, I had NEVER used the name it was showing as a Named Range in my Workbook. It ended up showing me that pop-up message about 10 times, all with different names -- none of which I had ever used.

I've been researching it for the past hour. I was able to write a sub that listed all of the ranges associated with names in the workbook (but unfortunately not the NAMES themselves. There was 931. Many of them were ones I had created, but I knew there were some I did not. One even referred to a different workbook entirely! (It is a workbook that I have on my computer, but I never created a range in it!) Here is the code I used:

Sub ListNames()

Dim nm As Name
Dim i As Integer

i = 1

For Each nm In ActiveWorkbook.Names

    ThisWorkbook.Sheets("Names").Range("A" & i).Value = nm
    
    i = i + 1

Next

End Sub

This resulted in showing me something like this for each entry: =Sheet1!$O$7
Unfortunately, it didn't show me the NAME itself that went along with the range. If anyone has any idea how to get it to do that, please let me know.

I read that if you pressed F3, you could paste all the named ranges that are in the workbook into the current cell you have selected. So, I did so. Doing this, it only listed 532 names. That seems to be about how many I have actually created in my workbook. (Yeah, it's complex workbook with lots of sheets.) So obviously there were MANY inside the workbook that are not showing up on the Name Manager, and some that I had never created. I noticed some were of duplicate ranges.

All of this leads me to a few questions:

1. Any idea why the sheet has so many more named ranges than appear in the Name Manager?

2. How can I delete the ones that are NOT in the Name Manager? The ones in the Name Manager are the only ones I need, and the others make me fear they will cause problems with my workbook down the road.

3. A specific problem -- two of the ranges that my code wrote to column A were just this: =#NAME?
I tried to delete these two using the below code:

Dim nm As Name

For Each nm In ActiveWorkbook.Names

    If nm = "=#NAME?" Then nm.Delete

Next

but I get the following error message:

Run-time error '1004':

The syntax of this name isn't correct.

Verify that the name:
- Starts with a letter or underscore
- Doesn't include a space or character that isn't allowed
- Doesn't conflict with an existing name in the workbook.

Any idea how I delete these two names? I wish I could see what their actual NAMES are, but as I mentioned above, the code I wrote above doesn't show me that part.

 

[MickG]

Try this for "Names and addresses"

Sub nam()
 Range("A1").ListNames
End Sub

 

[steve the fish]

If you want to delete based on the name then use nm.Name

 

[wideboydixon]

Try this implementation of your function:

Sub ListNames()

Dim nm As Name
Dim ws As Worksheet
Dim lo As ListObject
Dim i As Long

i = 1

For Each nm In ActiveWorkbook.Names
    ThisWorkbook.Sheets("Names").Range("A" & i).Value = nm.Name
    If Left(nm.Name, 6) <> "_xlfn." Then ThisWorkbook.Sheets("Names").Range("B" & i).Value = "'" & nm.RefersTo
    ThisWorkbook.Sheets("Names").Range("C" & i).Value = "Workbook"
    i = i + 1
Next nm

For Each ws In ActiveWorkbook.Worksheets
    For Each lo In ws.ListObjects
        ThisWorkbook.Sheets("Names").Range("A" & i).Value = lo.Name
        ThisWorkbook.Sheets("Names").Range("B" & i).Value = lo.Range.Address
        ThisWorkbook.Sheets("Names").Range("C" & i).Value = ws.Name
        i = i + 1
    Next lo
Next ws

End Sub

Name manager also includes table references. You should be able to delete the ones you don't need programmatically using the nm.Delete statement - is that not working. For the two ranges that give an error, they could be there because if IFERROR() or IFNA() formulas - that seems to create extra ranges. You can't delete them.

WBD

 

[Yongle]

VBA below creates a list of all your named rages in a new sheet
- workbook level names are followed by worksheeet level names

Sub NamedRanges()
    Dim ws As Worksheet, sh As Worksheet, n As Name, r As Long
    Set sh = Worksheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    For Each n In ThisWorkbook.Names
        If InStr(1, n.Name, "!") = 0 Then
            r = r + 1
            sh.Cells(r, 1).Resize(, 3).Value = Array("workbook", n.Name, "'" & n.RefersTo)
        End If
    Next n
    
    r = r + 1
    For Each ws In ThisWorkbook.Worksheets
        For Each n In ws.Names
            r = r + 1
            sh.Cells(r, 1).Resize(, 3).Value = Array(ws.Name, n.Name, "'" & n.RefersTo)
        Next n
    Next ws
End Sub

 

[mcomp72]

Thanks for all the great responses.

Using what you each told me, I was able to generate a list of all of the Named Ranges, with Names, Ranges, and the scope of each.

It turned out, only about 80 were truly hidden. I had not made any of those, so they must have been brought over when I had a second workbook open at the time I was working on this one?

Anyway, using the below code, I was able to delete all the hidden ones.

Sub DeleteNames()

Dim nm As Name

On Error Resume Next

For Each nm In ActiveWorkbook.Names

    If nm.Visible = False Then nm.Delete

Next

On Error GoTo 0

End Sub

I had to put On Error Resume Next because there are two that are hidden that cannot be deleted, as WBD mentioned.

Once I was able to see the scope of each Name, I saw that the rest of the Names WERE created by me. I hadn't realized I had created over 800, but looking at the list of the remaining ones, I recognize them all.

Thanks very much for the help!!