Over 100,000 Named Ranges in Excel. Name Manager will not open. How can I get rid of them?

[Bobthewalrus]

Hello Everybody,

As you saw in the title, I'm having an issue with a excel file that was passed to me. It is a file that is updated daily by adding a new sheet, however, quickly after I started working with it, it began to hang and then eventually crash. I was trying to locate the source of the error and noticed that I could not open the name manager. After looking through this forum I came across this thread:

http://www.mrexcel.com/forum/excel-questions/787555-over-400-hidden-defined-names-leftover-3rd-party-api-names-almost-identical-how-can-i-remove-w-o-deleting-my-own-defined-name-ranges.html</SPAN>

I was able to use user mikeerickson's formula to at least count how many named ranges the file had and was amazed to see the file had 114,547 names in the workbook </SPAN></SPAN>

Looking back at a version from over a year ago I found that I could get into the name manger in a file that only had 31,000 names in it and saw that the majority of the names were global references that had the #REF error. I am fairly confident that 99.99% of these names have #REF errors so I thought I could use the following code to delete all of the bad names.

</SPAN>
[B]Sub DeleteDeadNames()</SPAN>[/B]
Dim nName As Name</SPAN>
    For Each nName In Names</SPAN>
        If InStr(1, nName.RefersTo, "#REF!") > 0 Then</SPAN>
            nName.Delete</SPAN>
        End If</SPAN>
    Next nName</SPAN>
[B]End Sub</SPAN>[/B]

</SPAN>


However, I get the error "Run time error 7 out of memory," centered on the For Each statement.</SPAN>

Does anyone have any potential solutions to this problem?</SPAN>


Excel 2010 32bit</SPAN>

 

[Jerry Sullivan]

Here's some code you can try on a copy of your workbook.

For every 1000 names processed, it will Save the file and display the number of remaining names to be processed in the Immediate Window of the VBE.

If Excel crashes during the process (it crashed once on my test), you can restart the code and it will resume pretty close to the point it crashed.

On my test, this process runs very slow at first and gets progressively faster as the number of names is reduced.

Sub DeleteDeadNames2()
   Dim nName As Name
   Dim lCount As Long
   
   With ActiveWorkbook
      For lCount = .Names.Count To 1 Step -1
         If lCount Mod 1000 = 0 Then
            Debug.Print lCount
            .Save
            DoEvents
         End If
        If InStr(1, .Names(lCount).RefersTo, "#REF!") > 0 Then
            .Names(lCount).Delete
        End If
      Next lCount
   End With
End Sub

There might be faster alternatives if you want to delete All the names such as deleting all the names from an XML version of the file.

 

[Bobthewalrus]

This worked perfectly. I let the macro run over night and when I came back in there were only 3000 names left. Thank you!