Forum Discussion
Adrees to the column/row end
I would like to form an address from a cell to the end of its column or row, for instance in a total on the top of a column whose extension covers the column below in full. This is different from a full column/row address (for instance A:A), as not all cells are included, only those starting from a predefined one.
A handy way is an addres from that cell to the last one in the column or row (for instance A5:A1048576). However, the last cell appears no special in the address, it just happens to be last. I tried to add/delete a row/column (to simulate future maintenance or improvement), and the address reimains all right, that is to the last cell. Maybe Excel recognises that the address is to the last cell and then preserves this. In all cases, this way is not elegant and covers the programmer's intention.
Is there a special way to address the last/first cell of a row/column? If such name existed and were, e.g., $Last, then addresses such as A5:A$Last would be possible.
Thank you.
3 Replies
INRIM As a variant, and since you give an example of adding a total in the top of the column, I assume you are working with numbers. The following formula will sum the range from A5 to the last cell with a number.
=SUM($A$5:INDEX(A:A,MATCH(99^99,A:A)))Similarly, if you want to sum e.g. from D5 to the last number of row 5, use this one:
=SUM($D$5:INDEX(5:5,MATCH(99^99,5:5)))Both will allow the range to include blanks.
Alternatively for the last
=XMATCH(,A:A)-1could be any values in the cells, assuming there are no blanks in between
If there are no blank cells between A5 and the last used cell in column A, you can use
=OFFSET($A$5, 0, 0, COUNTA($A$5:$A$1048576), 1)
to refer to the range from A5 to the last used cell in column A.
In VBA, you can use code like this:
Dim LastRow As Long Dim RangeA As Range LastRow = Range("A" & Rows.Count).End(xlUp).Row If LastRow < 5 Then LastRow = 5 Set RangeA = Range("A5:A" & LastRow)
