Mass Deletion of Range Names?

[Larry]

It's easy to create many range names all at once
using "insert/name/create" for a large number of selected
rows and/or columns of data having row and/or columns
headings. Once those ranges are created, is there any way
in EXCEL to do a mass delete of those range-names from the
spreadsheet? You can clear or delete the worksheet they
are on, but that doesn't delete them as range names in the
workbook. You can also go to the menu
and "insert/name/define" and then proceed to delete each
range-name individually, but it won't let you select and
delete all the range names at once, or perhaps I just
don't know how to do it. Any ideas short of just deleting
the entire workbook? Thanks!

 

[Jim Rech]

Excel doesn't have a tool to delete a group of names all at once. I think
you can do it with Jan Karel Pieterse's Name Manager on Stephen Bullen'
site:

http://www.bmsltd.co.uk/DLCount/DLCount.asp?file=NameManager.zip

 

[Don Guillett]

That and more

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

Sub HyperlinksOut()
ActiveSheet.Hyperlinks.Delete
End Sub

Sub ShapesCut()
For Each S In ActiveSheet.Shapes
S.Cut
Next
End Sub
'or
Sub shapescut1() 'Tom Ogilvy
ActiveSheet.Shapes.SelectAll
Selection.Delete
End Sub

 

[Larry]

Thanks Don, that worked pretty slick. Any suggestions how
a relative novice in macros can discover the existance of
things in EXCEL such as "Names"? Thanks again!

 

[Don Guillett]

Glad to help.
Hang around here for awhile to learn a LOT.
Most of what I know I got in places like this.

 

[Norman Harker]

Hi Larry!

If you want to delete all range names in a workbook at once you can
use an old Lotus command that Excel does not replicate in it's command
structure.

Use Tools > Options > Transition
Make sure that you have the / in the Microsoft Excel menu or help key
Put a dot in Lotus 123 Help
OK

Then use:
/rnr

But be warned!!! This does not come with a health warning and will not
undo.

But it sure beats doing them one at a time.

Personally? I'd use Jan Karel Pieterse's name manage or adapt Don's
code to give a warning.

This feature does not seem to be available in Excel 2003

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.