adjust range size??
 

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 rang
(delete the data in A4), is there some way in VBA that I can adjust th
size of the range to now be A1:A9 so that I can continue to use th
=PartsList for validation? Thanks

--
ricksim
-----------------------------------------------------------------------
ricksimm's Profile: http://www.excelforum.com/member.php...fo&userid=2189
View this thread: http://www.excelforum.com/showthread.php?threadid=38239

adjust range size??
 

PartsLists is a defined name.
to change what the name refers to use:

insert/ name/ define...

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ricksimm wrote :


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.

adjust range size??
 

you can define the PartsList name with this function (in
insert/name/define...):
=OFFSET(Sheet1!$A$1;0;0;COUNTA(Sheet2!$A:$A);1)
and it will automaticaly change its size depending on how many items
there are in the A column

adjust range size??
 

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