Delete all Range Names

John117

Active Member
Joined
Sep 29, 2004
Messages
371
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows
Try:
Code:
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

Well-known Member
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows
Okay - I thought about it a bit, and you can get to the same place with:
Code:
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,730
Members
410,702
Latest member
clizama18
Top