Deleting all named ranges except "Print_Area"

[JOZZ]

I'm trying to write a short bit of code to delete all named ranges in a workbook EXCEPT the system-generated named range for Print_Area.

I've had some suggestions from Dave at Ozgrid (thanks Dave for your good suggestions)... but no perfect solution.

Used Instr(.. "Ref!")>0 but only deletes named ranges with Ref!
Used If nName.Name <> "Print_Area" but doesn't prevent Print_Area from being deleted.
Used nName.Visible = False but that didn't appear to delete any named ranges.
:dunno :doh:

.. your help would be most appreciated!!

Cheers

[lucas]

please provide us with a link or url to the cross post and read our FAQ. We don't really want to work on a problem that may already be solved since you posted here.

I can't see why the following would not work if you used it correctly it should not delete the named range "print area"
If nName.Name <> "Print_Area" 'but doesn't prevent Print_Area from being deleted.

[lucas]

This will work but the problem is in the naming of the Print_Area named range. You will have to name it PrintArea instead. I don't know why. Shouldn't make any difference though as you are using a named range for your print area and can name it anything you want within the rules for named ranges.
Sub RemNamedRanges()

Dim nm As Name

For Each nm In ActiveWorkbook.Names
If nm.Name <> "PrintArea" Then
nm.Delete
End If

Next

End Sub

[JOZZ]

Hi Lucas,
Thanks for the quick reply. Clearly, I am a newbie and don't know protocol (although I did read the FAQ items listed when signing up) - sorry.
The reference to Ozgrid was in direct email communication I had with Dave. I have not posted this question to any other forum. [so I don't know what url you would have me provide..?!] (i have deliberately NOT posted this question on any other forum for exactly the point you make).

As to why the <>"Print_Area" code doesn't work .. I don't know (although I've tried it a couple of times). Maybe because the "Print_Area" named range is system generated (and not defined by any user)???

[lucas]

Hi Jozz,
Thanks for the reply. I understand better now and apolgize if I seemed rude.....many don't take the time to read the faq and it's fairly short and to the point about most issues.

I believe you are correct about the system generated name causing the problem....see my earlier post for a workaround...it works, I tried it.