How can I delete all named ranges in a workbook or worksheet?

  • Thread starter Thread starter AZSteve
  • Start date Start date
A

AZSteve

I have 5-15 sheets in a workbook and I want to delete all the names (25 or
so) in each sheet in the workbook. I have not figured out a way to do this
reliably yet with a macro. Then I need to do it again on the next workbook.
Suggestions?
 
Sub test()

Dim ws As Worksheet
Dim nm As Name

For Each ws In Worksheets
For Each nm In ws.Names
nm.Delete
Next nm
Next ws

End Sub
 
Try the below

Sub DeleteNames()
Dim nmRange As Name
For Each nmRange In ActiveWorkbook.Names
nmRange.Delete
Next
End Sub

If this post helps click Yes
 
There appear to be certain range names that you should not delete, so here
is a macro that preserves them if they are present and deletes all the
rest...

Sub DeleteNames()
' First seen posted by Bob Phillips
Dim N As Name
For Each N In ActiveWorkbook.Names
If N.Name Like "*_FilterDatabase" Or _
N.Name Like "*Print_Area" Or _
N.Name Like "*Print_Titles" Or _
N.Name Like "*wvu.*" Or _
N.Name Like "*wrn.*" Or _
N.Name Like "*!Criteria" Then
Else
N.Delete
End If
Next N
End Sub
 
I want to delete all names, so the other posts gave me that answer. However
I will save yours for when I want to keep certain names. Thanks.
 
The list of names that Rick avoided deleting wasn't for names that he assigned.

These are names that excel uses (without your permission and usually without
your knowledge). If you delete any of them, you may be breaking a feature built
into excel.

I'd use Rick's version.
 
Back
Top