Removing Range Names

K

k9deb

I use the following macro to remove all the range names in a workbook:

Sub RemoveNamesAll()
For Each N In ActiveWorkbook.Names
N.Delete
Next N
End Sub


As indicated it removes ALL names. However I would like to change thi
so that it removes all, except for "Print_Area" and "Print_Titiles".
have tried to modify the macro so that those two are not removed
without success.

Does anybody have any ideas
 
C

Chip Pearson

Try something like the following code:

Dim N As Name
For Each N In ActiveWorkbook.Names
If N.Name <> "Print_Area" And N.Name <> "Print_Titles" Then
N.Delete
End If
Next N


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
N

Norman Jones

Hi ,

Try:

Sub RemoveNamesAll()
Dim N As Name
For Each N In ActiveWorkbook.Names
If Not N.Name Like "*Print_*" Then
N.Delete
End If
Next N
End Sub
 
K

k9deb

Norman's macro works - removes all name ranges, except for Print_Are
and Print_Titles

Sub RemoveNamesAll() ' change name AllExceptPrint
Dim N As Name
For Each N In ActiveWorkbook.Names
If Not N.Name Like "*Print_*" Then
N.Delete
End If
Next N
End Sub

Course one can modify the ...Like "*Print_"... to keep other nam
ranges.


Chip - your macro still deleted all names ranges, the Print name range
included. So it does not fill ny need.

Thanks for replying

Don Bolstad K9DE
 
P

Peter T

I suspect the reason Chip's routine didn't work for you is
because the names "Print_Area" and "Print_Titles" are
reserved for use with Print setup. These become defined as
worksheet level names, even if you didn't explicitly
create as such. So these names would always exist like
this:

"Sheet1!Print_Area"

Apart from this the basic principle of Chip's sub is, of
course, correct.

Regards,
Peter
 
Top