How to delete names on worksheet?

[GOH]

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.

 

[Kevin B]

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...

 

[Pete_UK]

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

http://groups.google.com/group/micr...c/4d36d0964e4e333f?lnk=st&q=#4d36d0964e4e333f

Pete

 

[Dave Peterson]

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]

Saw it now. Thanks.

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

http://groups.google.com/group/micr...c/4d36d0964e4e333f?lnk=st&q=#4d36d0964e4e333f

Pete

 

[GOH]

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".

 

[GOH]

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

 

[Pete_UK]

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

Hope this helps.

Pete

 

[GOH]

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

 

[Pete_UK]

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