How to delete all defined names from a workbook?

[Dmitry Kopnichev]

Hello
How to delete all defined names from a workbook?

 

[sfby]

The Name manager add in is very useful...

http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.htm

 

[Niek Otten]

Use the Name manager by Jan Karel Pieterse.
You download it here:

http://www.jkp-ads.com/Download.htm

 

[Dmitry Kopnichev]

Thank you!

 

[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)

 

[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
(e-mail address removed)

 

[Dmitry Kopnichev]

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

 

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

 

[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????

 

[Dmitry Kopnichev]

I do not try again yet.

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

 

[Dmitry Kopnichev]

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

 

[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)

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

 

[Dave Peterson]

I sent a message to Jan Karel. He would like to see your workbook.

You can send it to him at: (e-mail address removed)

 

[Dmitry Kopnichev]

Thanks!
What exactly could I be breaking that excel needs, if I delete those names?

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]

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

 

[Dave Peterson]

Jan Karel told me it was ok to post that address. (I would have munged it,
too.)