How to delete named range?

  • Thread starter Thread starter lukus2005
  • Start date Start date
L

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
 
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
 
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
 
Back
Top