Specifying range from A2 till infinity (NO VBA)

Question

Without VBA, I am trying to refer a range that starts at A2 and never ends. For example, if I want row 2 till row 5 i'd do

$A$2:$A$5

But what if I want the end to be open?

$A$2:??

Is this possible?

Solution 1

Depending on what's in A1 and what formula you're putting the reference into, you could simply use A:A. For example, if you wanted to sum all of the values in column A, but A1 contained a column title rather than a number, you could still write =SUM(A:A) and the title in A1 would just be ignored.

Solution 2

A2:A works in many formulas hope that helps

Solution 3

Another option (in case your formula is in A1, so accessing A:A would create a circular reference) is:

OFFSET(A2, 0, 0, ROWS(A:A)-1)

This uses ROWS to count the total number of rows (without actually accessing the rows!), subtracts 1 (because we're starting with the second row), and uses this result as the height of a range created with OFFSET.

Solution 4

If you want to refer to a range starting from A2 until max row (1048576 or 65536 for Excel prior to 2007), you can use this volatile formula... =OFFSET(A2,0,0,(COUNTBLANK(A:A)+COUNTA(A:A)-1),1) . Use formula as a defined range name or inside other formula which takes range as an argument (for eq SUM)...

View more solutions

Comments

[FPcond]

I would just note that you can drag this down and use it to fill cells if you just change it to OFFSET($A$2, 0, 0, ROWS(A:A)-1)

[tdpu]

I haven't found a situation where this works (In Excel functions)... I would be thrilled if it were implemented, the current workarounds are horrifyingly complex for a simple need.