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

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?

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

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


"AZSteve" wrote:

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?

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

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
---------------
Jacob Skaria


"AZSteve" wrote:

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?

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

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

--
Rick (MVP - Excel)


"AZSteve" wrote in message
...
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?

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

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.

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"AZSteve" wrote in message
...
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?

.

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

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.

AZSteve wrote:

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.

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"AZSteve" wrote in message
...
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?

.


--

Dave Peterson