Delete Name Ranges Except Print Area and Print Titles

[nhinx]

Dear Excel Experts,

I'm trying to built in the following code in excel to prevent having a lot of unwanted name ranges in my excel workbook. The code below worked for me but always deleting the Print Titles which kinda frustrating since I always edit files with print titles (Rows to repeat at top) before printing. I tried including the statement If Right(n.Name, 10) <> "Print_Titles" Then n.Delete to the code below but it also delete the Print Area of all of the sheets in the workbook. Can anyone help me solve this issue?

Private Sub Worksheet_Activate

Dim n As Name
On Error Resume Next
For Each n In ActiveWorkbook.Names
n.visible=true
Debug.Print n.Name

If Right(n.Name, 10) <> "Print_Area" Then n.Delete
If InStr(1, n.RefersTo, "=#NAME?") > 10 Then n.Delete
If InStr(1, n.RefersTo, "=#REF!#REF!") > 10 Then n.Delete
If InStr(1, n.RefersTo, "=#REF!") > 10 Then n.Delete
If InStr(1, n.RefersTo, "=#N/A") > 10 Then n.Delete
If InStr(1, n.RefersTo, "=#N/A,#N/A") > 10 Then n.Delete
If InStr(n.Value, "=#REF!") > 10 Then n.Delete
If InStr(n.Value, "=#REF!#REF!") > 10 Then n.Delete
If InStr(n.Value, "=#N/A") > 10 Then n.Delete

Next n
On Error GoTo 0

End Sub

Thank you and Kind regards,

Nhinx

 

[Excelogist]

Usually the Name "print_area" is of Worksheet Level. ....

dim nm as Name
for each nm in Me.Names'Me = WorkSheet
    if nm.Name not Like "*Print*Area*" then nm.Delete
next

 

[Excelogist]

correction:
if Not nm.Name Like "*Print*Area*" then nm.Delete then

 

[nhinx]

correction:
if Not nm.Name Like "*Print*Area*" then nm.Delete then

Hi Excelogist,

Thanks for the code, your code and my code above are both working. My only problem is the Print Titles not to be deleted during the run. The Print Titles correspond to the rows repeating at top during printing. Say I have a worksheet of 20 pages and during the printing I want row 1 to 6 to be always printed from Page 2 to 20. So that is my dilemma because the Print Area is there but the Print Titles is always removed once the code was executed.

correction:
if Not nm.Name Like "*Print*Area*" then nm.Delete then

 

[Excelogist]

Hi Excelogist,

Thanks for the code, your code and my code above are both working. My only problem is the Print Titles not to be deleted during the run. The Print Titles correspond to the rows repeating at top during printing. Say I have a worksheet of 20 pages and during the printing I want row 1 to 6 to be always printed from Page 2 to 20. So that is my dilemma because the Print Area is there but the Print Titles is always removed once the code was executed.

Sorry, my bad. I didn't notice it's "Print 'Title' ". How about :

If Not (n.Name like "*Print*Area*") or Not (n.Name like "*Print*Title*") Then
    If InStr(1, n.RefersTo, "=#NAME?") > 10 Then n.Delete
    If InStr(1, n.RefersTo, "=#REF!#REF!") > 10 Then n.Delete
    If InStr(1, n.RefersTo, "=#REF!") > 10 Then n.Delete
    If InStr(1, n.RefersTo, "=#N/A") > 10 Then n.Delete
    If InStr(1, n.RefersTo, "=#N/A,#N/A") > 10 Then n.Delete
    If InStr(n.Value, "=#REF!") > 10 Then n.Delete
    If InStr(n.Value, "=#REF!#REF!") > 10 Then n.Delete
    If InStr(n.Value, "=#N/A") > 10 Then n.Delete
end if

 

[Alex Blakenburg]

@Excelogist - you need AND

 

[Excelogist]

@Excelogist - you need AND

Yes, "And".

 

[Alex Blakenburg]

@nhinx - not sure that was clear the line should read.

If Not (n.Name like "*Print*Area*") And Not (n.Name like "*Print*Title*") Then

or using your initial methodology:

    If Right(nm.Name, 10) <> "Print_Area" And Right(nm.Name, 12) <> "Print_Titles" Then

 

[nhinx]

Yes, "And".

@nhinx - not sure that was clear the line should read.

If Not (n.Name like "*Print*Area*") And Not (n.Name like "*Print*Title*") Then

or using your initial methodology:

    If Right(nm.Name, 10) <> "Print_Area" And Right(nm.Name, 12) <> "Print_Titles" Then

Hi Alex,

Both code is working. Though the first one has still more name range with #REF! and #N/A left in the name manager but the second one only few name range with #REF! and #N/A has left and could manage to manually delete it. I've tested it in the excel with a thousands name ranges which make my excel work slow. Thank you very much for your help. It saves my day. You guys rock.