Macro to delete names except Print_Area

[barberboy]

I have a macro that deletes all of the named ranges in my workbook. The
problem is that it also deletes the "Print_Area" range, which results
in resetting my print area.

Is there a way to modify this so that it will keep my old print area?

Sub DeleteNames()
'
' Gets rid of all named ranges
'
For Each nName In Names
nName.Delete
Next
'
End Sub

 

[Ardus Petus]

Sub DeleteNames()
'
' Gets rid of all named ranges except Print_Area
'
For Each nName In Names
if nName.Name <> "Print_area" then
nName.Delete
end if
Next
'
End Sub

HTH
'--
AP

 

[John]

Dear Barber,

Have a go with the code below. The Print_Range name gets prefixed with the
sheet it applies to so you need to use the 'Right' function to check the
last part of the returned string. So what it's saying is if the last 10
characters of each name do not end in "Print_Area" then delete them. (You
can delete the Debug.Print... line which just shows you what's going on in
the Intermediate window of the VBE.)

Best regards

John

Sub DeleteNames()
' Gets rid of all named ranges
Dim nName As Name

For Each nName In Names
Debug.Print nName.Name
If Right(nName.Name, 10) <> "Print_Area" Then
nName.Delete
End If
Next nName

End Sub

 

[Tom Ogilvy]

Sub DeleteNames()
'
' Gets rid of all named ranges
'
For Each nName In Names
if instr(1,nName,Name,"Print_Area",vbtextcompare) = 0 then
nName.Delete
end if
Next
'
End Sub

 

[Dave Peterson]

Typo alert patrol:

If InStr(1, nName.Name, "Print_Area", vbTextCompare) = 0 Then

(dot instead of a comma in nName.Name)

 

[Dave Peterson]

Be careful.

Excel creates names that it uses (without your knowledge and without your
permission).

If you delete those names, you could be breaking something that excel needs.

If I were you, I'd get a copy of Jan Karel Pieterse's (with Charles Williams and
Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You can delete just the names you want pretty easily.

 

[barberboy]

Thanks for your input guys. Both John's and Tom's macros worked great
for what i need.

And thanks Dave for the heads up on the excel-created named ranges and
the Name Manager add-in. While the Name Manager wasn't what i was
needing for this task, it is a valuable add-in. I was able to clear up
some redundant local names as well as get rid of some unused named
ranges. And i'm sure i'll find a use for it in the future as well.

Thanks again for all of your help. You guys are amazing.

Ben