Issues Deleting all Named Ranges -- Quite confused

[spydey]

I have 50 named ranges. Some of them are just a range, others have dynamic ranges (formulas). None appear to be incorrect. Everything is calculating correctly based off of the named ranges. I have used the Name Manager to filter for Names with Errors and there are none listed.

I need to remove/delete all the named ranges from the Name Manager, via VBA.

I can use the code below, but I get an error:

Sub deleteAllNames()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


Dim xName As Name


For Each xName In Application.ActiveWorkbook.Names
       xName.Delete
Next


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

The error is this:

Run-time error '1004':
The Name that you entered i not valid.

Reasons for this can include:

• The name does not being with a letter or an underscore
• The name contains a space or other invalid characters
• The name conflicts with an Excel built-in name or the name of another object in the workbook

My options are "End" or "Debug".

Debugging takes me to this line:

xName.Delete

The named ranges stay in the Name Manager and nothing changes in the formulas in the cells.

I have tried this code too:

Sub DeleteNamesRanged()

Dim counter
Dim nameCount


nameCount = ActiveWorkbook.Names.Count
counter = nameCount
Do While counter > 0
ActiveWorkbook.Names(counter).Delete
counter = counter - 1
Loop


End Sub

It does the same thing, gives me the same error, but I seem to get a little closer to my goal.

When it fails and I debug, it takes me to:

ActiveWorkbook.Names(counter).Delete

The Locals window shows the counter value at 9. So it goes from 50 down to 10 successfully, removing each Named range from the Name Manager. Then it fails at 9.

Thing is, I noticed that it doesn't remove the Named Ranges in any particular order that I can discern. It starts at the bottom of the alphabetical list, but then after a while there is no pattern. So I can't figure out which named range is # 9.

The other thing is that after failing ... even though it says it failed on # 9, all of the Named Ranges are gone from the Name Manager. So really it succeeded but is popping up the error message.

The only way I seem to be successful at getting the end result is by copying and pasting values for all sheets via this code:

Sub Saveasvalue()

    Dim wsh As Worksheet
    For Each wsh In ThisWorkbook.Worksheets
        wsh.Cells.Copy
        wsh.Cells.PasteSpecial xlPasteValues
    Next
    Application.CutCopyMode = False
End Sub

Then saving the file, then closing it.

After reopening it, I can then run either of the first codes I posted above to remove the Named ranges, and they work flawlessly. All named ranges are removed, and no error messages at all.

But, I have to close it and then reopen it to get it to work.

If I copy and then paste values, then save it, then run either of the first two codes from above, even though all of the named ranges are gone from the cells, I still get the error message.

What would be causing the error message?
Why do I need to close and then re-open the workbook to have it work?
How do I figure out which named range is # 9? Is it the same each time or does it change each time the code is run?

I really am not sure where to go from here. I have tried debugging the best I can by trying to figure out where the error lies, approaching it from different angles. I seem to be getting close but am not sure where to go from here.

Any ideas? Thoughts? Suggestions?

-Spydey

 

[shg]

You're welcome.