Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a variable range
I have a range that starts in cell A6. Columns extend out
to AH6. My rows vary depending on volume input. How do I select this variable range? Headers are contained in rows 1 to 5. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a variable range
use something akin to this from http://www.contextures.com/xlNames01.html#Dynamic create a name (insert name define and then In the Refers To box, enter an Offset formula that defines the rang size, based on the number of items in the column, e.g.: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) In this example, the list is on Sheet1, starting in cell A1 The arguments used in this Offset function a Reference cell: Sheet1!$A$1 Rows to offset: 0 Columns to offset: 0 Number of Rows: COUNTA(Sheet1!$A:$A) Number of Columns: 1 Note: for a dynamic number of columns, replace the 1 with: COUNTA(Sheet1!$1:$1) this would create a range name for a1 to the last value in column -- duan ----------------------------------------------------------------------- duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162 View this thread: http://www.excelforum.com/showthread.php?threadid=27442 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a variable range
thanks Duane, worked great.
-----Original Message----- use something akin to this from http://www.contextures.com/xlNames01.html#Dynamic create a name (insert name define and then In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in the column, e.g.: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) In this example, the list is on Sheet1, starting in cell A1 The arguments used in this Offset function a Reference cell: Sheet1!$A$1 Rows to offset: 0 Columns to offset: 0 Number of Rows: COUNTA(Sheet1!$A:$A) Number of Columns: 1 Note: for a dynamic number of columns, replace the 1 with: COUNTA(Sheet1!$1:$1) this would create a range name for a1 to the last value in column a -- duane ---------------------------------------------------------- -------------- duane's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=274421 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro syntax for selecting variable range | Excel Discussion (Misc queries) | |||
Selecting a variable range | Excel Programming | |||
Selecting a variable Range | Excel Programming | |||
Selecting Variable Row Range w/Macro | Excel Programming | |||
Selecting a Range Using a Variable | Excel Programming |