Clearing all cell names, easily.

[Karl Roseman]

I have a workbook, that contains 500+ defined cell names.
I want to create an easily emailable copy of the data, by
removing all the names from the work book, and just
showing the end result data. I have copied all the pages
into a new book, and now I would like to delete all the
names.. but it seems I can only individually choose each
name, and click delete.. this will take FOREVER... can you
please tell me, anyone, if there is ANYWAY to delete the
defined cell names EASILY??? I am running EXCEL 200

 

[JS]

The following macro will delete all names in a workbook

Sub DeleteNames()
Dim n As Long
Dim m As Long

m = ThisWorkbook.Names.Count

For n = 1 To m
ThisWorkbook.Names(1).Delete
Next n
End Sub

 

[Norman Harker]

Hi Karl!

Simplest way is to use an old Lotus keystroke command that works in
Excel but which doesn't have an equivalent without VBA.

Tools > Options > Transition
Select the Lotus 1-2-3 Help option

Thereafter:

/RNR

Will clear all of your names including those set by Excel such as
Print_Area.

But watch out. There's no, "Are you sure!" And there's no Undo
capability. But if this is what you want, it sure beats doing it one
at a time.

A more user friendly approach is used by Rob Bovey in Excel Utilities
1.5 obtainable from:

Rob Bovey
http://www.appspro.com/

Apart from this clearance of all names there's a lot more other useful
"everyday" utilities included.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Tuesday: Belgium, Botswana, British Virgin Islands,
Burundi, Canada, China, French Polynesia, Ghana, Haiti, Hong Kong,
Rwanda, Somalia, St. Eustatius, St. Vincent & the Grenadines, Surinam,
Taiwan, Thailand.
Celebrations Tuesday: Ratha Yatra (Chariot Journey): Hinduism. Rosh
Hodesh Tammuz (last day): Judaism
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.