Hello All,

I need to define the range of a drop down list in some dynamic way.
am adding rows to the top of the range and I need to redefine the rang
whenever a new row is added. I tried simply playing with absolute v
relative refs, but it seems to redefine itself when I use the list (n
idea why). Regardless, I'd rather do it all in code so I can be sur
of what is happening.

I can use the following code to redefine my named range:

ActiveWorkbook.Names("range_name").RefersTo =
"='Sheet Name'!$B$4:$B$6"

But, I need to make the new range a variable so that it covers B4:B
after one row is added, and B4:B8 after the second row is added and s
forth. Basically, it needs to begin at B4 and extend to include ever
cell below that contains a value.

Thanks!

--
Message posted from http://www.ExcelForum.com

Hi William

Look at Debra's page for Dynamic range
http://www.contextures.com/xlNames01.html


--
Regards Ron de Bruin
http://www.rondebruin.nl


"William DeLeo " wrote in message ...
Hello All,

I need to define the range of a drop down list in some dynamic way. I
am adding rows to the top of the range and I need to redefine the range
whenever a new row is added. I tried simply playing with absolute vs
relative refs, but it seems to redefine itself when I use the list (no
idea why). Regardless, I'd rather do it all in code so I can be sure
of what is happening.

I can use the following code to redefine my named range:

ActiveWorkbook.Names("range_name").RefersTo =
"='Sheet Name'!$B$4:$B$6"

But, I need to make the new range a variable so that it covers B4:B7
after one row is added, and B4:B8 after the second row is added and so
forth. Basically, it needs to begin at B4 and extend to include every
cell below that contains a value.

Thanks!!


---
Message posted from http://www.ExcelForum.com/

There's a complete answer for ya ... thanks so much (again)

--
Message posted from http://www.ExcelForum.com