VBA - Remove all Named Ranges

[spydey]

I am trying to remove all named ranges/defined names in my workbook.

I was pretty sure that it would be simple.

I am using the following code:

Sub DeleteNames()


Dim xn As Name


For Each xn In Application.ActiveWorkbook.Names
    xn.Delete
Next
End Sub

However, I get an error message:

Run-time error '1004':

The name that you entered is not valid.

Reasons for this can include:

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

I have reviewed all the named ranges and none of them appear to fall into those reasons.

A majority of my named ranges use index in the name so that they are dynamic ranges; but I doubt that would be the issue.

Any suggestions or ideas as to why I would be getting this issue? I am racking my brain but can't seem to get it.

-Spydey

 

[mikerickson]

I avoid using potential column names as variable. Try changing "xn" to "someName"

 

[spydey]

I avoid using potential column names as variable. Try changing "xn" to "someName"

Thanks Mike for the insight. Sorry it has taken me a bit to get back.

Here is the code that I got to work for what I needed:

Sub DeleteNames()

Dim RangeName As Name


On Error Resume Next
For Each RangeName In Names
    ActiveWorkbook.Names(RangeName.Name).Delete
Next
On Error GoTo 0
End Sub

Cheers!

-Spydey