Remove all named ranges except print area from a spreadsheet

[DrSmyth]

Hi,

I want to run some code that will remove all named ranges except the print area from an excel spreadsheet. So far I have this:

    Dim nm              As Name
     
    On Error Resume Next
    For Each nm In ActiveWorkbook.Names
    If ActiveWorkbook.Name = "Print_Area" Then Resume Next
        nm.Delete
    Next
    On Error GoTo 0

But it deletes the print area. Does anybody have any suggestions?

 

[SkipVought]

hi,

    Dim nm As Name
     
    For Each nm In ActiveWorkbook.Names
       If Not nm.Name Like "*Print_Area" Then nm.Delete
    Next

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[PHV]

Perhaps this ?

For Each nm In ActiveWorkbook.Names
    If nm.Name <> "Print_Area" Then nm.Delete
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DrSmyth]

Cheers Skip, it works!!

Just one more thing though (in the words of columbo)... Running this code seems to stop auto filters from working, you click on the down arrow and nothing happens, my guess is that there's some kind of hidden range name that is being deleted, because of this you have to take them off and then re-apply to get it to work again.

Any ideas how I can resolve this, or shall I just add a line into the routine to remove/re-apply the autofilters.

Cheers

 

[PHV]

Check the value of nm.Visible

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rmikesmith]

DrSmyth,

Yes, Autofilter does create a hidden name, something of the form Sheet2!_FilterDatabase. So you could use a variation on Skip's theme:

    Dim nm As Name
     
    For Each nm In ActiveWorkbook.Names
       If Not nm.Name Like "*Print_Area" And Not nm.Name Like "*FilterDatabase" Then nm.Delete
    Next

Regards,
Mike

 

[SkipVought]

Check out faq707-4594.

By putting a BREAK in your loop, and then observing the nm object properties, you can learn ALOT!

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}