Name Manager does not open

[Ron512]

In an Excel 2013 workbook when attempting to open Name Manager (Formulas > Name Manger) I receive the progress circle for a few seconds than nothing.
I have searched the forum and internet with to luck.

Anyone have ideas?

Thanks
Ron

 

[shg]

Sub MostlyNoNames()
  Dim i             As Long

  With ActiveWorkbook
    For i = .Names.Count To 1 Step -1
      Select Case .Names(i).Name
        Case "Bob", "Joe", "MyRange"
        Case Else
          .Names(i).Delete
      End Select
    Next i
  End With
End Sub

 

[Ron512]

shg, Works great, thanks. I owe you a beer.

 

[shg]

You're welcome.

 

[Khauff]

Hi guys,

i am having the same issue and i tried using the code that shg provided. how long should it take to run it? for me it says running and then - module (code)} Not responding.

 

[Khauff]

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


i tried using thig but then i get run time error 1004 the syntac of this name isnt correct

 

[tjr35]

You're welcome.

I am having a similar issue. There are a ton of error messages for broken (phantom) links in a workbook with many sheets copied in from different users (not done with a paste values - it wasn't me!). I ran the VBA that counts the number of named ranges, and it came up with about 140k. I am now running the code that purges all those names, but have no idea how long I can expect it to run for before aborting the mission and doing it in batches with the other bit of code. How long should I expect this code to take to delete 140k names? It's been about 20 minutes already.

Thanks!!

 

[Rcon97]

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

shg,

I am having the same issue as Ron512 with the code provided by Mr. Moehling. When I run this, it seems to work, but after about 5-6 times running the macro (deleting 1,000 names per run), I get a I receive a “subscript out of range error”. It sounds like this version worked for Mr. Moehling, but for me it runs for a few seconds then my excel stops responding. Any suggestions on how to delete with this? I'm dealing with a financial model that has 150,000+ names and cannot open the name manager. I'm getting desperate - would appreciate any help you could provide. Thanks in advance!

 

[Scriffer]

Bumping this thread because it helped me clean a file with >300K buried ranges, which from my googling might be a record.

I've got another file I'm scrubbing, but I'm erroring out because many names have invalid characters, causing me to get the Run-time 1004 syntax error:
"Verify 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"

My guess it's the second option that's breaking things down. Can anyone think of a workaround here? Here's are the two script formats I currently have running. I get the same error both ways:

Thanks, y'all

SHG Version:
Sub MostlyNoNames()
'Name Manager does not open
Dim i As Long

With ActiveWorkbook
For i = .Names.Count To 1 Step -1
Select Case .Names(i).Name
Case "Print_Area"
Case Else
.Names(i).Delete
End Select
Next i
End With
End Sub


Mr Moehling version
Sub Delnames_00()
'Name Manager does not open
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 = ActiveWorkbook.Names.Count To 1 Step -1
Select Case ActiveWorkbook.Names(i).Name
Case "Print_Area"
Case Else
ActiveWorkbook.Names(i).Delete
End Select
If i = 100000 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