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


"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?

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?

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" <(E-Mail Removed)> wrote in message
news:813457AE-0D24-4ADB-AA44-(E-Mail Removed)...
>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?

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" <(E-Mail Removed)> wrote in message
> news:813457AE-0D24-4ADB-AA44-(E-Mail Removed)...
> >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?
>
> .
>

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" <(E-Mail Removed)> wrote in message
> > news:813457AE-0D24-4ADB-AA44-(E-Mail Removed)...
> > >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