How to delete all define names in Excel

[lehainam]

Dear all,

In order to delete all define name in Excel I create a below macro:

Sub NamTest()


For i = 1 To ActiveWorkbook.Names.Count

 MyName = ActiveWorkbook.Names.Item(i).Name
 ActiveWorkbook.Names(MyName).Delete

Next
End Sub

However, there is an error message 'This name is not valid'.

Could anyone can help me.

Thanks

Nam

[rylo]

Hi

You have to work in descending order when deleting names.

For i = ActiveWorkbook.Names.Count to 1 step -1
  ActiveWorkbook.Names(MyName).Delete
Next i

rylo

[lehainam]

Hi rylo,

Thanks.

I try to do your code but the same problem still happens.

Could any one can help me.

Thanks,

Nam

[rylo]

Hi

Silly me forgot to modify the code fully....

Try

For i = Application.Names.Count To 1 Step -1
    Application.Names(i).Delete
  Next i

rylo

[lehainam]

Dear roly,

Thanks for your help.

I tried to apply your VBA code but I still have the same problem.

Regards,

Nam

[jonn]

This is the code put together as roly suggested.

Sub NamTest()

For i = Application.Names.Count To 1 Step -1
MsgBox (i)
    Application.Names(i).Delete
  Next i

End Sub