I want to create a template in Excel with a function that includes a range, but the number of cells in this range will change every time the template is applied.
How can I identify that I want the range to start at say, B2 and end at the last cell with text in column B?
Best Answer
This depends on what you're doing, and as such it would help if you added a bit more detail to your question. As it is we can't tell what you're trying to do, or why.
As such I can only advise you on how to create automatic (dynamic) ranges:
1
Use a table - Excel tables are incredibly powerful, and automaticaly count as a range, if you want to refer to every cell in a column just enter [columnname] rather than the traditional cell reference. Excel tables automaticaly expand as new rows are added too, which is pretty handy.
The donwside of tables brings us back to the question of what this template is, and what it's supposed to do: if the tamplate is supposed to be accessed by users of Excel 2003 or earlier tables won't work.
2
Which brings me to option 2: Dynamic Named Ranges.
This Dyanmic Named Range, when refered to in a formula, will return everything from the range stated in the offset to the second half of the range stated in the COUNTA. The formula looks at the entire range in the COUNTA to get the number of not blank cells and then creates an offset range selection from the first cell, down to the number of cells found.
More info on Dynamic Named Ranges here: http://www.ozgrid.com/Excel/DynamicRanges.htm
More info on Tables here: http://www.ozgrid.com/Excel/data-tables.htm
A few things to keep in mind RE Dynamic Named Ranges: