I'm using a function to delete all the names in vba but I have an error 1004-CodePudding

I got this piece of code from https://www.extendoffice.com/

Sub DeleteNames()
'Update 20140314
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
    xName.Delete
Next
End Sub

But when I try to run it give me an error 1004 (syntax of this name is incorrect) on the line xName.Delete

CodePudding user response：

The problem comes from internal Names that are issued from Excel and start with an underscore. As far as I know, all those names start with _xlfn. They are a hint that an Excel file was created with a newer version of Excel and then opened with an older version that doesn't support some features (eg functions in a formula), see for example here

Those Names cannot be deleted. I guess the error message is a little misleading, it would be better if there would be a message like "this object cannot be deleted.". Anyhow, the workaround is easy, either check the name of that Name or enclose the deletion with an error handler:

For Each xName In Application.ActiveWorkbook.Names
    if left(xName.Name, 1) <> "_" Then xName.Delete
Next

For Each xName In Application.ActiveWorkbook.Names
    On Error Resume Next
    xName.Delete
    On Error Goto 0
Next

CodePudding user response：

The error is that it try to delete "hidden" internal Name ranges that start with a _xlfn which I cannot remove. so I use a Split to check if it starts with _xlfn and if it don't, then it means I can delete it.

Sub DeleteNames()
    Dim xName As Name
    For Each xName In Application.ActiveWorkbook.Names
        If Split(xName.Name, ".")(0) <> "_xlfn" Then
            xName.Delete
        End If
    Next
End Sub