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?

Aemia

Excel 2007 has 1048576 rows, so if, by infinity, you meant something less than or equal to that, then you probably want the end to be dynamically determined (such as the last used cell in the column, or the last non-blank cell in the current contiguous range)? Is that what you're looking for? Do you want it to refer to a cell in the same column? And, where do you want to use this? It'd be better if you could specify the situation you're working on. – Sansom

Not recommended to use up all the rows as that will slow down your performance. Name a range using INDEX():INDEX() should take care of most situations. OFFSET is another way but it is a volatile function which will also consume more PC power than regular functions. Just my two cents. – Uralite

Answer 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.

Rolland

Answer 2

This is another option based on a formula, using the example locations in the OP's question:

=A2:INDEX(A:A,MAX(FILTER(ROW(A:A),IF(ISBLANK(A:A),0,1)=1)))

The components are the following:

1. =MAX(FILTER(ROW(A:A),IF(ISBLANK(A:A),0,1)=1)) which finds the number of the deepest row that is not blank, and
2. A2:INDEX(A:A,<expression 1 above>) which relies on the expression above to make a bigger formula, which obtains a range starting from any location and ending at a location in the given column at the position obtained by this expression, 1.

This is an alternative to the others listed, and may be of interest as it differs from them in potentially substantial ways.

I can note the following characteristics:

• It is not necessarily fast.
• It seems to NOT be a volatile formula. This is important, as it means it won't necessarily be recalculated every time a calculation is made. However, I am not sure about the frequency of calculation, and don't fully understand its volatility status. The uncertainty is related the use of the INDEX function (and, apparently, specifically after the : in a range). There are some resources that describe it. INDIRECT and OFFSET functions are definitely volatile. There are a number of resources that describe performance implications of volatile functions, some of them mentioned in other SO answers. For example:

https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation https://www.sumproduct.com/thought/volatile-functions-talk-dirty-to-me http://www.decisionmodels.com/calcsecretsi.htm https://chandoo.org/wp/handle-volatile-functions-like-they-are-dynamite/

• It allows the user to not have to think about the data in certain cells (for example, A1, which may be meant to have a header, and not numbers).
• It returns a range between the cell specified before the : and the last cell in the column that is non-blank. I think it should include non-numeric values in its consideration as well.
• It shares some commonality in terms of the range it aims to identify with the answer by Kresimir L.: =OFFSET(A2,0,0,(COUNTBLANK(A:A)+COUNTA(A:A)-1),1).

To note: This answer applies to the version of Excel available as of the time of writing as part of Office 365 (and continually updated). However, the answer is based only on my own verification of its apparent correctness of my installation. I am not sure that all installations of Office 365 have the same software exactly; and I have the sense that some features may differ among different installations (even) of Office 365. I am not sure that this answer applies to everyone. Please test. I would appreciate feedback on your success with this approach.

Wendalyn

Answer 3

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)...

Imf

Answer 4

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.

Chacma

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) – Aelber

This is the solution I'm looking for, but it doesn't work in my Excel for some reason. For starters, arguments need to be separated by a semi-colon. After fixing that, it throws the infamous #REF! error. The cell containing the result is not in the range I'm trying to select, so I have no idea what the problem is. – Adiaphorous

Answer 5

In Excel 365, with a spilled formula you can simply use A2# and it will refer to A2 and all following cells in range until the end.

Fic

Only if the values are the results of a spilled formula. =SEQUENCE(4) in A2 you can refer with =A2#, 1,2,3,4 manually typed into A1:A5 will return a #REF! error if referenced using A2# – Exordium

Thank you for the clarification, I've edited my answer – Fic

Answer 6

A2:A works in many formulas hope that helps

Tammara

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. – Nita

In my experience this only works in Google Sheets, not in Excel. – Woermer

Answer 7

This well covered in VBA as code below:

Range("A2", Range("A2").End(xlDown))

And if you want reach that in formula, it depends on the version number of your MS-Excel.

According to this reference number of all rows are in a sheet from Excel 2007 onwards are 1048576 that you can use bellow:

$A$2:$A$1048576

Because this range in formula is depended on Excels version, this may be different in future versions.

Finally, I suggest you use VBA.

Delano