Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a ' } ' into excel so that I can adjust its size? | Excel Discussion (Misc queries) | |||
adjust automatically size margins | Excel Discussion (Misc queries) | |||
How do I adjust the font size in a Combo Box? | Excel Discussion (Misc queries) | |||
Adjust the size of cells automatically | Excel Discussion (Misc queries) | |||
How would I adjust size for printing | Excel Discussion (Misc queries) |