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

 

[Ron512]

The users are rebuilding the workbooks.

Steve, That is a great question. I think because the workbook has several hundred sheets with many of them having been copied from other workbooks located on a server so links were created as well as bringing over the names from the other workbooks. On the files I can open Name Manager there are a lot of #REF names as well as the links to other workbooks on the server.

So how do I prevent this issue?
Does it make sense to execute a macro on file close to delete all names not intended to be there?
Is there a way to prevent the links at the moment a sheet is copied?

Any suggestion will be appreciated.

Thanks

Ron

 

[steve the fish]

Firstly don't use paste. Use paste special values. Then you can use fluffs code to use on workbook close to clean up any spurious named ranges. It's how you manage legitimate named ranges that you then have to solve so you don't end up deleting what you want.

 

[Ron512]

The users are opening two workbooks, from the source workbook they right click the sheet they intend to copy, select Move or Copy, check the create a copy box, and choose the destination workbook in the to book drop down. Is there a alternate or preferred way to copy a sheet for one workbook to another?

 

[steve the fish]

You will be bringing over all data names associated with that sheet and links into the new sheet. Is that intentional? I'm going to presume not. I'd be doing a copy and paste special. You can select all cells in the top left by cell A1. That way you will just be copying the value of the cells not the links etc.

 

[Ron512]

I follow using the paste special values only although the sheets have formatting both text and numerical, borders etc, and formulas that must be retained. I tried several paste special options and all have created links other than values only but of course no formatting or formulas are copied.

Am I missing something?

 

[Mr Moehling]

Hi All, stumbled upon this thread trying to fix a file that was sent from another department. I think i found the solution: You need to refer to the names ranges using an index number instead of the name.

Sub Delnames_00()
    Dim i As Long
    Dim LastCount As Long
    Dim StartTime As Double
    Dim SecondsElapsed As Double
        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
    SecondsElapsed = Round(Timer - StartTime, 2)
    MsgBox "Batch done - Removed: " & LastCount - ActiveWorkbook.Names.Count & " named ranges " & vbNewLine & vbNewLine _
        & "Run Time: " & Int(30 / SecondsElapsed) & " minutes and " & SecondsElapsed Mod 60 & " seconds"
End Sub

 

[Mr Moehling]

Sorry messed up the timer code in my haste... see the simplified fix below:

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

 

[Ron512]

Mr Moehling

Thanks for resurrecting this old post. Your solution does work and I’m very gratefully.

I do have questions on tweaking the code so it would better fit my situation. The workbooks I’m dealing with have a huge number of named ranges that need to be deleted. Your code has a limit of 500 deletions so I attempted to increase that.

In an attempt to delete all names I adjusted the code, by removing the statement ”If i = 500 Then Exit For” with the thought that the For/Next will loop through the full count although I receive a “subscript out of range error”.

I also tried increasing the number in the same statement “If i = 500 Then Exit For” , it was successful but at about 50,000 I again received the error “subscript out of range error”.

Any thoughts or remedies will be appreciated.

Ron

 

[shg]

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

 

[Ron512]

Thanks sng, it works perfectly.
If I may push this a bit farther, how would I delete all the names except a specify one?