Delete all Range Names

[John117]

Hello
I am trying to delete ALL range names from WS. I recorded macro but I have a hard time to convert it to delete all of then at once.
Could you please help me with this?

Thank you
John

 

[dcardno]

Try:

Sub DelNames()

Dim i          As Integer

For i = ActiveWorkbook.Names.Count To 1 Step -1
    ActiveWorkbook.Names(i).Delete
Next i

End Sub

You have to step "backwards" through the names collection, otherwise when you delete '.names(1)' all the index referencing changes - there are now only N-1 names to delete, and what used to be the '.names(2)' range is now '.names(1)' - and will not be deleted! Trying to iterate through from 1 to 'names.count' will leave you with a subscript error, and ~half of the names you were trying to delete still in place.

 

[dcardno]

Okay - I thought about it a bit, and you can get to the same place with:

Sub DelNames2()

Dim Nm         As Name

For Each Nm In ActiveWorkbook.Names
    Nm.Delete
Next Nm

End Sub

Obviously this avoids the issue of the changing index entirely, although that is still an issue to keep in mind - for instance, when iterating through rows in a worksheet...