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.


.. 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"
[VBA]If nName.Name <> "Print_Area" 'but doesn't prevent Print_Area from being deleted.
[/VBA]

[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.
[VBA]Sub RemNamedRanges()

Dim nm As Name

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

Next

End Sub[/VBA]

[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.