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]

When the code breaks, enter

? xName.Name

in the Immediate window. What do you see?

 

[Fluff]

If you use something like this

Sub DelNames()
   Dim Nme As name
   For Each Nme In ActiveWorkbook.names
      Debug.Print Nme
      Nme.Delete
   Next Nme
End Sub

The names will appear in the immediate window.
When it errors out, the last name shown is the one causing a problem

 

[spydey]

@ shg

That is awesome!! I had no idea I could do that! Thank you a ton.

Ok, so I ran it, and it came back with:

_xlfn.AVERAGEIFS

-Spydey

 

[spydey]

@ Fluff

When I run the code you provided, I get:

=#NAME?

It looks like it is deleting things in a different order now than my second coding ......

-Spydey

 

[spydey]

System issues - Double post.

Sorry.

 

[shg]

That's a name created by Excel for a workbook that contains an AVERAGEIFS function in a version of Excel that didn't support it (i.e., pre Excel 2007, I think).

Try this and see what's left in Name Manager

Sub deleteAllNames()
  Dim xName         As Name

  On Error Resume Next
  For Each xName In ActiveWorkbook.Names
    xName.Visible = True
    xName.Delete
  Next xName
End Sub

 

[spydey]

@shg

Ok, so I tried it, and it worked!! No error message and it removed all the named ranges. Interestingly enough, when I went to go check again in the Name Manager, there are 2 named ranges that I didn't create and were not previously there:

_xlfn.AVERAGEIFS

_xlfn.COUNTIFS


I do use those two functions quite a bit in the in-cell formulas.

And everything originally came over from an Excel 2003 file, so that does make sense, even though now it is an excel 2013 file (.xlsx).

Is there anything I should do to clean those up?

If the code you provided is working as expected now, I think I might be able to manage from here.

I will simply copy and paste all sheets as values and then run the code and save the workbook as a copy, thus giving me my original and a copy without in-cell formulas and named ranges.

Suggestions? Thoughts? Ideas?

-Spydey

P.S. Thanks a ton shg!! That was a huge help!!

 

[shg]

I think they'll go away when you exit compatibility mode (save, close, and reopen).

 

[spydey]

I think they'll go away when you exit compatibility mode (save, close, and reopen).

Correct, they have been removed by closing and re-opening.

Thank you again shg for your help!!

-Spydey