3

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

TylerH
  • 20,799
  • 66
  • 75
  • 101
Mona Dandan
  • 31
  • 3
  • 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 Sep 23 '19 at 19:22
  • 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 Sep 23 '19 at 19:31
  • Can you figure out any more details about which of the names causes the error? Using its `.Name` for example? – BigBen Sep 23 '19 at 19:33
  • @MonaDandan, what was the first named range – AAA Sep 23 '19 at 19:34
  • _x._V is the first name – Mona Dandan Sep 23 '19 at 19:42
  • @MonaDandan, can you go to your Names collection and see which range that refers to? It may be a built-in name, hence the error – AAA Sep 23 '19 at 19:54
  • My bad, it said _xlfn._FV --- looks like this is the problem! "xlfn is a prefix added to functions that don't exist in the version of Excel that you are using" looks like I have a bigger issue than I originally thought. I will look into this. Thanks everyone. – Mona Dandan Sep 23 '19 at 19:57

1 Answers1

0

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
Mona Dandan
  • 31
  • 3