I have about 3000+ define name in my workbook. It was not created in this
workbook. But somehow they are just there. The workbook does link to many
files. Now I want to clear them all out. I have used the following VB codes
posted in this website to delete all these define names.

Sub DeleteAllNames()
Dim objName As Excel.Name
For Each objName In ActiveWorkbook.Names
objName.Delete
Next objName
End Sub

However, it gives me the "Run-time error '1004': that name is not valid.

Can anyone please help me how to do this? Thanks a lot.

I compiled and ran that code with no problems, and it removed the named
ranges. When you get the run-time error, do you get the option to
"end" or "debug" the code? If yes, then choose debug and post what
line / command is causing the code to choke.

There are lots of names that excel uses (hidden from view). And you could
destroy one of those names if you run code like this.

If this is a one time thing, I'd use Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager to clean things up.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You can use it to be much more selective when you're cleaning.

But this may give you a hint:

Option Explicit
Sub DeleteAllNames2()
Dim objName As Excel.Name
For Each objName In ActiveWorkbook.Names
On Error Resume Next
objName.Delete
If Err.Number < 0 Then
MsgBox objName & " Wasn't deleted"
Err.Clear
End If
On Error GoTo 0
Next objName
End Sub

(Jan Karel's name manager will get rid of those illegal names, too.)


sunshine wrote:

I have about 3000+ define name in my workbook. It was not created in this
workbook. But somehow they are just there. The workbook does link to many
files. Now I want to clear them all out. I have used the following VB codes
posted in this website to delete all these define names.

Sub DeleteAllNames()
Dim objName As Excel.Name
For Each objName In ActiveWorkbook.Names
objName.Delete
Next objName
End Sub

However, it gives me the "Run-time error '1004': that name is not valid.

Can anyone please help me how to do this? Thanks a lot.

--

Dave Peterson