How to delete named range?

[lukus2005]

The named ranges are no longer valid as I'm tweaking my dependent list
pulldowns but I can't seem to be able to delete or rename the named
ranges that I've set up already. It won't let me re-define the range
either.

So is there a way to delete all of the named ranges?

TIA

 

[Pete_UK]

With the file open do Alt-F11 to bring up the VBE and then CTRL-G to
go into the Immediate Window. Type the following line in full (or copy/
paste it from here):

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

then press <enter>. This should get rid of them all in one go,
including any print areas you might have set up.

Hope this helps.

Pete

 

[Dave Peterson]

If you're working with names, get 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'll find it very valuable.

 

[GB]

Thanks Pete. I didn't know you could separate statements on the same line
with colons. Very useful.

Incidentally, the following code prints out the referred to ranges
For Each nme In ActiveWorkbook.Names : debug.print nme : Next nme

To get the names of the ranges, you need:
For Each nme In ActiveWorkbook.Names : debug.print nme.Name : Next nme

It was a pure guess on my part to add the .Name bit to get the names. I
couldn't find it in vba help (I'm using excel 2003). Could someone tell me
where to find it there, or what I should be looking for. I thought it would
be under properties, but Name is not listed in the help.




With the file open do Alt-F11 to bring up the VBE and then CTRL-G to
go into the Immediate Window. Type the following line in full (or copy/
paste it from here):

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

then press <enter>. This should get rid of them all in one go,
including any print areas you might have set up.

Hope this helps.

Pete

 

[Mike Middleton]

GB -

For a non-VBA way to get a list of Names and RefersTo, in Excel 2003, select
an out-of-the-way cell, and choose Insert | Name | Paste | Paste List.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Bob Phillips]

or directly from Jan Karle's site
http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp

--
HTH

Bob

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

 

[Bob Phillips]

That should be Jan Karel of course, apologies to him

--
HTH

Bob

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

or directly from Jan Karle's site
http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp

--
HTH

Bob

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