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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,638
Office Version
365
Platform
Windows
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:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,664
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

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,638
Office Version
365
Platform
Windows
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 :(:cry:
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,638
Office Version
365
Platform
Windows
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,664
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?
 

Forum statistics

Threads
1,081,574
Messages
5,359,707
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top