1004 error on Macro to Delete All Names in ActiveWorkbook

Mitchdp2

New Member
Joined
Oct 31, 2019
Messages
2
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!

Code:
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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 ...

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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 ...

Code:
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!
 
Upvote 0
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
Code:
    n.Name = "NameABC"
or using the index
Code:
    ActiveWorkbook.Names(3).Name = "NameDEF"
but it does not work for those names :sad::cry:
 
Last edited:
Upvote 0
to keep the code running and ignoring those ranges, try
Code:
        On Error Resume Next
        If Right(n.Name, 11) <> "!Print_Area" And n.Name <> "Print_Area" Then n.Delete
        On Error GoTo 0
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top