Name Manager not opening - Macros not working to fix

[Rcon97]

Hello,

I'm dealing with a financial model with 150,000+ names (errors and links that can't be broken) and cannot open the name manager. In a separate thread, I came across the below macro which seems to help and I can run a handful of times before I get an error of either “subscript out of range error” or the name range / format is incorrect. Diving even deeper into the thread, I found the second line of code which just freezes up my excel "Excel (Not Responding)" and I think I will have to force quit. Does anyone have any tricks, suggestions, or macros that have worked for similar issues? Getting desperate here, so would appreciate any and all advice!

Sub Delnames_00()
    Dim i As Long
    Dim LastCount As Long
    Dim StartTime As Double
    StartTime = Timer
        LastCount = ActiveWorkbook.Names.Count
        Debug.Print "Starting Count: " & ActiveWorkbook.Names.Count
        Application.Calculation = xlCalculationManual
            For i = 1 To ActiveWorkbook.Names.Count
                ActiveWorkbook.Names(i).Delete
                If i = 500 Then Exit For
            Next i
        Application.Calculation = xlCalculationAutomatic
    MsgBox "Batch done - Removed: " & LastCount - ActiveWorkbook.Names.Count & " named ranges " & vbNewLine & vbNewLine _
        & "Run Time: " & Format$((Timer - StartTime) / 86400, "h:mm:ss") & " h:mm:ss"
End Sub


Sub NoNames()
  With ActiveWorkbook
    Do While .Names.Count
      .Names(.Names.Count).Delete
    Loop
  End With
End Sub

 

[Zot]

Yes -- I can do it in batches of 4 - 5 runs at 5000 names, then I get the error message. That's with my hitting save, closing the file, re-opening, and re-running the macro. Once I get the error message, it won't let me run the macro even after closing the file / re-running the macro.

I don't have newer Excel than 2016 but one thing I observed is that if in case of macro crash you need to close all instances of opened Excel, not just the crashed one. Otherwise, macro will not run unlike previous Excel.

What you want the end result is? Do you want to removed all ranged names and all the external link or just the ranged name and keep all the links? This might help others out there to devise possible solution.

 

[Rcon97]

Grasping at straws a bit here but see if this makes any difference.

Sub DeleteBadRefs()
 
  Dim nm As Name
 
  For Each nm In ActiveWorkbook.Names
    If InStr(1, nm.RefersTo, "#REF!") > 0 Then
      nm.Delete
    End If
  Next nm
End Sub

Certainly in the first instance, as I mentioned previousy we can try a file that has ClearConent applied to all sheets, so I can't see any confidentiality issues with that unless you think there is confindention information inside the name manager.

Thanks, Alex. I still got the "run time error '7 out of memory" with this one unfortunately. Thank you everyone for trying to help out. I was able to cut the file size in ~ half using the Macabacus name scrubber function (Macabacus > File > optimize > name scrubber). There are still a few phantom links that I cannot break, but the file is opening much quicker and is not extremely slow anymore which was the end goal in the first place.

 

[Alex Blakenburg]

Thank you for letting us know. I had not heard of that product before. I wonder if it has something to do with you not being able to open name manager.
Glad you were able to get it sorted.

 

[Zot]

Good that you have found solution. My Excel crashed every time I tried to save big file ...