1004 error on Macro to Delete All Names in ActiveWorkbook

[Mitchdp2]

Hi- My Company's software macro creates hidden names in our workbooks making them clunky and unnecessarily large, so I am trying to get a macro to first unhide all names in the name manger, then delete all, except the print range. Except I am gettin a 1004 name syntax error on the n.Delete portion of the second sub below - thinking it may be because some of the names generated include items with special characters (i.e. _Order 1 and random symbols to start the names). Please let me know if you have a fix or can tell me what I'm doing wrong and thanks!

Sub ShowAllNames()Dim n As Name
For Each n In ActiveWorkbook.Names
n.Visible = True
Next n
End Sub


Sub DeleteAllRangesExceptPrintArea()
  Dim n As Name
  For Each n In ActiveWorkbook.Names
    If Right(n.Name, 11) <> "!Print_Area" And n.Name <> "Print_Area" Then [B]n.Delete[/B]
  Next n
End Sub

 

[Yongle]

it may be because some of the names generated include items with special characters

Or is it an Excel built-in name that cannot be deleted like that in VBA (expect 1st character of name to be " _ ")
Let's find out ...

Sub DeleteAllRangesExceptPrintArea()
    Dim n As Name
    For Each n In ActiveWorkbook.Names
        [COLOR=#ff0000]Debug.Print n.Name[/COLOR]
        If Right(n.Name, 11) <> "!Print_Area" And n.Name <> "Print_Area" Then n.Delete
    Next n
End Sub

See Immediate Window in VBA editor with {CTRL} G

Look in the Immediate Window after running the code
- if the code fails, then there is a problem with the last Name listed

 

[JoeMo]

After you run your ShowAllNames macro go to the Name Manager. Those names that begin with "_" may be the ones that are causing the 1004 error. They can be deleted in the Name Manager.

 

[Mitchdp2]

Or is it an Excel built-in name that cannot be deleted like that in VBA (expect 1st character of name to be " _ ")
Let's find out ...

Sub DeleteAllRangesExceptPrintArea()
    Dim n As Name
    For Each n In ActiveWorkbook.Names
        [COLOR=#ff0000]Debug.Print n.Name[/COLOR]
        If Right(n.Name, 11) <> "!Print_Area" And n.Name <> "Print_Area" Then n.Delete
    Next n
End Sub

See Immediate Window in VBA editor with {CTRL} G

Look in the Immediate Window after running the code
- if the code fails, then there is a problem with the last Name listed

As expected, yes the issue is the names that being with _ such as _Order1.

Is there a way to rename these prior to the Delete function so that I can then delete these as well and not get the error? I don't want to do it manually as suggested since this will be a recurring issue across all our internal workbooks.

Thanks!

 

[Yongle]

Is there a way to rename these prior to the Delete function ?

I am not aware of any method in VBA to allow those named ranges to be renamed

Usually you can do it like this
as in your code

    n.Name = "NameABC"

or using the index

    ActiveWorkbook.Names(3).Name = "NameDEF"

but it does not work for those names

 

[Yongle]

to keep the code running and ignoring those ranges, try

        On Error Resume Next
        If Right(n.Name, 11) <> "!Print_Area" And n.Name <> "Print_Area" Then n.Delete
        On Error GoTo 0

 

[JoeMo]

As expected, yes the issue is the names that being with _ such as _Order1.

Is there a way to rename these prior to the Delete function so that I can then delete these as well and not get the error? I don't want to do it manually as suggested since this will be a recurring issue across all our internal workbooks.

Thanks!

Don't think you can delete them via VBA, but did you see post#3?