Deleting named ranges with VBA

[davesweep]

Hi All,

I am trying to use a little code to delete named ranges from a workbook.

Sub DeleteAllNames()

Dim nName As Name

For Each nName In ActiveWorkbook.Names
nName.Delete
Next nName

End Sub

However, on running the code, I get the dreaded runtime error 1004 telling me the name I have entered is invalid. None of the named ranges are deleted. By putting a break in at line 4, print nName.name returns _xlfn.IFERROR and hovering over the nName variable shows "=#NAME?", yet none of the named ranges contain an IFERROR function.

I fear this might be a result of Mondayitis, but I am truly stumped.

 

[VoG]

Is this any better?

Sub DeleteAllNames()
Dim i As Long
For i = ActiveWorkbook.Names.Count To 1 Step -1
    Range(ActiveWorkbook.Names(i).Name).Name.Delete
Next i
End Sub

 

[davesweep]

Hi Peter,

I still get the error with your code.

I have worked around by using On Error Resume Next, but I would rather understand the issue than hide it.