I have many names inherited from copying worksheet from external files, the
file now contains hundreds of names that is giving me some problems. Apart
from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody
know how I can delete all the names completely.

Press Alt+F11 to open the VB editor.

In the VB Editor click on INSERT in the menu and select MODULE

In your new module enter the following macro:

Sub ClearNames()

Dim wb As Workbook
Dim n As Name

For Each n In ThisWorkbook.Names
n.Delete
Next n

Set n = Nothing

End Sub

Save the macro by click on FILE in the menu and selecting SAVE

Press Alt + Q to return to the workbook or click FILE in the menu and select
CLOSE AND RETURN...

From the workbook press Alt + F8, select the macro named ClearNames and
click the RUN button.

Hope this helps...
--
Kevin Backmann


"GOH" wrote:

I have many names inherited from copying worksheet from external files, the
file now contains hundreds of names that is giving me some problems. Apart
from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody
know how I can delete all the names completely.

Did you not see these answers to your duplicate post from a few days
ago?

http://groups.google.com/group/micro...36d0964e4e333f

Pete

On May 21, 2:33*pm, GOH wrote:
I have many names inherited from copying worksheet from external files, the
file now contains hundreds of names that is giving me some problems. Apart
from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody
know how I can delete all the names completely.

I'd use Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:

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

You may want to keep some of the names that you defined -- or keep the names
that excel uses that you didn't know even existed.

GOH wrote:

I have many names inherited from copying worksheet from external files, the
file now contains hundreds of names that is giving me some problems. Apart
from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody
know how I can delete all the names completely.

--

Dave Peterson

Saw it now. Thanks.

"Pete_UK" wrote:

Did you not see these answers to your duplicate post from a few days
ago?

http://groups.google.com/group/micro...36d0964e4e333f

Pete

On May 21, 2:33 pm, GOH wrote:
I have many names inherited from copying worksheet from external files, the
file now contains hundreds of names that is giving me some problems. Apart
from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody
know how I can delete all the names completely.

Thanks for your help. I tried, following your instruction but it resulted in
a "run-time error '1004'. that name is not valid.". When I debug, it
highlighted "n.Delete".



"Kevin B" wrote:

Press Alt+F11 to open the VB editor.

In the VB Editor click on INSERT in the menu and select MODULE

In your new module enter the following macro:

Sub ClearNames()

Dim wb As Workbook
Dim n As Name

For Each n In ThisWorkbook.Names
n.Delete
Next n

Set n = Nothing

End Sub

Save the macro by click on FILE in the menu and selecting SAVE

Press Alt + Q to return to the workbook or click FILE in the menu and select
CLOSE AND RETURN...

From the workbook press Alt + F8, select the macro named ClearNames and
click the RUN button.

Hope this helps...
--
Kevin Backmann


"GOH" wrote:

I have many names inherited from copying worksheet from external files, the
file now contains hundreds of names that is giving me some problems. Apart
from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody
know how I can delete all the names completely.

I followed your instruction but it resulted in an error "run-time error
'1004': that name is not valid."


"Pete_UK" wrote:

Did you not see these answers to your duplicate post from a few days
ago?

http://groups.google.com/group/micro...36d0964e4e333f

Pete

On May 21, 2:33 pm, GOH wrote:
I have many names inherited from copying worksheet from external files, the
file now contains hundreds of names that is giving me some problems. Apart
from deleting one at a time using 'Insert'-'Name'-'Define', wonder anybody
know how I can delete all the names completely.

Just check that you have typed everything in correctly, including the
spaces.

Hope this helps.

Pete

On May 21, 3:26*pm, GOH wrote:
I followed your instruction but it resulted in an error "run-time error
'1004': that name is not valid."

I copied your instruction over, so shouldn't have missed out anything.


"Pete_UK" wrote:

Just check that you have typed everything in correctly, including the
spaces.

Hope this helps.

Pete

On May 21, 3:26 pm, GOH wrote:
I followed your instruction but it resulted in an error "run-time error
'1004': that name is not valid."

Okay then, I've just done this in a workbook with named ranges. Try it
like this:

For Each nme In ActiveWorkbook.Names : nme.Delete : Next nme

Copy this into the immediate window, then press <enter - it worked
for me.

Hope this helps.

Pete

On May 22, 2:56*pm, GOH wrote:
I copied your instruction over, so shouldn't have missed out anything.



"Pete_UK" wrote:
Just check that you have typed everything in correctly, including the
spaces.

Hope this helps.

Pete

On May 21, 3:26 pm, GOH wrote:
I followed your instruction but it resulted in an error "run-time error
'1004': that name is not valid."- Hide quoted text -

- Show quoted text -