If the range is A1:A10, and you delete A4, you don't need to do anything,
Excel will automatically adjust it for you.

You get a problem where you change the data at the start or the end, adding
rows to either, as they will not automatically get included. This can easily
be addressed by using dynamic ranges, such as

OFFSET($A$1,0,0,COUNTA($A:$A)-1)

--
HTH

Bob Phillips

"ricksimm" wrote in
message ...

I am using a data validation list defined as a range using =PartsList.
If the range was initially set up as A1:A10 and I change the range
(delete the data in A4), is there some way in VBA that I can adjust the
size of the range to now be A1:A9 so that I can continue to use the
=PartsList for validation? Thanks.


--
ricksimm
------------------------------------------------------------------------
ricksimm's Profile:
http://www.excelforum.com/member.php...o&userid=21892
View this thread: http://www.excelforum.com/showthread...hreadid=382390