Excel names in workbook - how to delete all?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GOH - 19 May 2008 11:07 GMT

How do you delete all the 'names' defined @ workbook? I can only do it 1 at
time now using 'Insert'-'Name'-'Define'......however, I have hundreds of them
(accumlated unknowingly, when I copied 3rd party worksheet - it seem that the
names juz get copied over each time you copy from another file).

Thanks.

Reply to this Message

Max - 19 May 2008 11:16 GMT

Sub DeleteNames()
Dim nm As Object
For Each nm In ActiveWorkbook.Names
nm.Delete
Next
End Sub

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> How do you delete all the 'names' defined @ workbook? I can only do it 1 at
> time now using 'Insert'-'Name'-'Define'......however, I have hundreds of them
> (accumlated unknowingly, when I copied 3rd party worksheet - it seem that the
> names juz get copied over each time you copy from another file).
>
> Thanks.

Reply to this Message

Bob Phillips - 19 May 2008 12:25 GMT

Sub DeleteNames()
Dim nme As Name

For Each nme In ActiveWorkbook.Names
If nme.Name Like "*_FilterDatabase" Or _
nme.Name Like "*Print_Area" Or _
nme.Name Like "*Print_Titles" Or _
nme.Name Like "*wvu.*" Or _
nme.Name Like "*wrn.*" Or _
nme.Name Like "*!Criteria" Then
Else
nme.Delete
End If
Next nme

End Sub

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> How do you delete all the 'names' defined @ workbook? I can only do it 1
> at
[quoted text clipped - 5 lines]
>
> Thanks.

Reply to this Message

Pete_UK - 19 May 2008 12:57 GMT

You can type this little routine into the immediate window - Alt_F11
to bring up the VBE, then CTRL-G to get the Immediate Window, then
type:

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

Press <Enter> at the end of the line, and all your names will be
deleted.

Hope this helps.

Pete

Reply to this Message

Bruce Sinclair - 21 May 2008 05:30 GMT

>How do you delete all the 'names' defined @ workbook? I can only do it 1 at
>time now using 'Insert'-'Name'-'Define'......however, I have hundreds of them
>(accumlated unknowingly, when I copied 3rd party worksheet - it seem that the
>names juz get copied over each time you copy from another file).
>
>Thanks.

If you work with names a bit, try the name manager add on. Google suggests
http://www.jkp-ads.com/officemarketplacenm-en.asp

HTH

Reply to this Message