excelvbarange

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

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

Solution

  • 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