Why do I get a 1004 Error when I loop through ActiveWorkbook.Names and try to delete Named Ranges using VBA?

2
votes

I have a macro that needs to delete all named ranges in my Workbook. Every time I run it, I get a 1004 Run-time error unless I include an error handler. When I include an error handler, it works and actually deletes all of the named ranges. Why does that happen? For context, the code I am showing below is an excerpt of a sub.

This works and does not produce an error:

Dim nm As Name
Dim wb3 As Workbook
Set wb3 = ActiveWorkbook

For Each nm In wb3.Names
    On Error GoTo Skip
    nm.Delete
Skip:
    On Error GoTo 0
Next

This gives the 1004 error:

Dim nm As Name
Dim wb3 As Workbook
Set wb3 = ActiveWorkbook

For Each nm In wb3.Names
    nm.Delete
Next

The error occurs at nm.Delete

1
excelvbanamed-ranges
I don't have this error. Can you step through your code and see what triggers the error? Also add this line to your code just before the nm.Delete : Debug.Print nm.Name – AAA
I get the error. It printed the first named range. It bugs at nm.Delete ...Could it be the version of Excel I am using? I am using Excel from Office '16 – Mona Dandan
Can you figure out any more details about which of the names causes the error? Using its .Name for example? – BigBen
@MonaDandan, what was the first named range – AAA
_x._V is the first name – Mona Dandan

1 Answers

0
votes

The problem was that Excel was trying to delete some functions that it created that started with _xlfn.

The following code worked with no errors:

Dim nm As Name
Dim wb3 As Workbook
Set wb3 = ActiveWorkbook

For Each nm In wb3.Names
    If Not Left(nm.Name, 6) = "_xlfn." Then
        nm.Delete
    End If
Next