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

 

[Chrisdontm]

I would try closing the program and opening it again...
If that doesn't do it, I would try the shortcut for opening it: Hold the Ctrl key and tap the F3 key...

If that doesn't work, I would try it in another workbook and give up on that first workbook...
But that's just me...

 

[Fluff]

If you run this, what does the message box say?

Sub NameCount()
   MsgBox ActiveWorkbook.names.Count
End Sub

 

[Ron512]

Fluff, thanks for the reply. Wow, the value I receive is 208,174.00. The file should have 23 named ranges.

 

[Fluff]

Ouch. That's a lot of names.
Try

Sub Delnames()
   Dim Nme As Name
   For Each Nme In ActiveWorkbook.names
      Nme.Visible = True
      Select Case Nme.Name
         Case "pcode1", "Fluff"
         Case Else
         Nme.Delete
      End Select
   Next Nme
End Sub

Change the vlaues in red to match the names you want to keep

 

[Ron512]

I make the changes to the macro you provide to include the names to keep. I tested, it worked fine. When I executed it on the issue file it would run for about 20 minutes than excel would crash.

Any other ideas?

 

[Fluff]

Try doing it in batches

Sub Delnames()
   Dim Nme As Name
   Dim i As Long
   For Each Nme In ActiveWorkbook.names
      i = i + 1
      Nme.Visible = True
      Select Case Nme.Name
         Case "pcode1", "Fluff"
         Case Else
         Nme.Delete
      End Select
      If i = 1000 Then Exit For
   Next Nme
   MsgBox "Batch done"
End Sub

Save & close the file after each batch. If it does the first batch quite happily you can change the value in red to a higher number if needed.

 

[Ron512]

The attempt to run it in batches was not successful. I tried lowering the number to as few as ten. I receive the runtime error out of memory. With the thought that it was computer memory I closed all applications and unnecessary processes as well as rebooted to clear any residual RAM with the same result. I tried on two computers and several files that have the same issue with similar negative results. Now starting to get a bit frustrated.

 

[Fluff]

In that case I think about the only option would be to re-build the workbook by copying the the info into a new workbook.
You would need to copy the cells, rather than the sheets.

 

[steve the fish]

How did you end up with 200k named ranges?