How to delete all defined names from a workbook?

B

Bob Phillips

VBA

Sub DeleteNames()
Dim nme As Name


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


End Sub




Here is another way, previously posted by Norman Harker. But beware, it
doesn't work with Excel 2003.


And here's a best kept secret of Excel but it comes with a health warning.


Tools > Options > Transition

Check Lotus 123 Help
Check Transition Navigation Keys


Now:
/RNR


Removes every single name in the workbook!


Beats doing them one at a time although you can use VBA or various Add-Ins.


But it really ought to come with a health warning after the /RNR command as
it can't be undone! And with many hundreds of names in a workbook

Just close the Names dialog box when it comes up


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Don Guillett

This will do it.

Sub DeleteAllNames() 'ALL sheets
For Each Name In Names
Name.Delete
Next Name
End Sub

or

Sub DeleteHiddenNames()
Dim n As Name
Dim Count As Integer
For Each n In ActiveWorkbook.Names
If Not n.Visible Then
n.Delete
Count = Count + 1
End If
Next n
MsgBox Count & " hidden names were deleted."
End Sub
Don Guillett
SalesAid Software
[email protected]
 
D

Dmitry Kopnichev

The Name manager caused Excel to shut down without saving files. The error
appeared when I tried to delete inoperable names.
 
D

Dave Peterson

Be careful.

Excel creates names that it uses (without your knowledge and without your
permission).

If you delete those names, you could be breaking something that excel needs.

If I were you, I'd get a copy of 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 can delete just the names you want pretty easily.
 
D

Dave Peterson

You may want to contact Jan Karel:
http://www.jkp-ads.com/Contact.htm

If the file doesn't contain any proprietary information, I'm sure he would like
to have a copy (confirm with him first) so that he can see the problem.

If the file does contain proprietary info, maybe you can clean it up so that the
data is gone, but the problem is still there????
 
D

Dmitry Kopnichev

I do not try again yet.
Dave Peterson said:
You may want to contact Jan Karel:
http://www.jkp-ads.com/Contact.htm

If the file doesn't contain any proprietary information, I'm sure he would like
to have a copy (confirm with him first) so that he can see the problem.

If the file does contain proprietary info, maybe you can clean it up so that the
data is gone, but the problem is still there????
Click to expand...
 
D

Dmitry Kopnichev

Thanks.
How to know which names Excel created? What exactly could I be breaking that
excel needs, if I delete those names?
 
B

Bob Phillips

They usually take the format of


*_FilterDatabase
*Print_Area
*Print_Titles
*wvu.*
*wrn.*
*!Criteria

most will recreate next time needed, but not guaranteed.

--

HTH

RP
(remove nothere from the email address if mailing direct)


Dmitry Kopnichev said:
Thanks.
How to know which names Excel created? What exactly could I be breaking that
excel needs, if I delete those names?
Click to expand...
 
D

Dmitry Kopnichev

Thanks!
What exactly could I be breaking that excel needs, if I delete those names?
Bob Phillips said:
They usually take the format of


*_FilterDatabase
*Print_Area
*Print_Titles
*wvu.*
*wrn.*
*!Criteria

most will recreate next time needed, but not guaranteed.

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
D

Dmitry Kopnichev

Thanks!
Does not publishing an e-mail address in a message a group cause spam to the
address?
 
Top