Last non-empty cell from column range A to Z

2
votes

Does anyone know the formula to find the value of the last non-empty cell in a whole sheet(with A1 : Z2500 Range), in Microsoft Excel?

I'm able to get the last non-empty cell for A column with below formula.

=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))

but not able to get for whole range from A1 to Z2500 please help if anyone know the solution of it.

1
excelexcel-formula
@Thelouras This question is not duplicate I don't want empty cell from the only column but from A1:Z2500 range. – KARAN
ok , remove flag – Thelouras
@ADMIN so how have you tried to adapt any of the answers? There are several possibilities... or do you just expect to be handed a solution on a plate??? – Solar Mike
@SolarMike Sorry but I have tried with =ROW(OFFSET(A1,COUNTA(A:A)-1,0)), =SUM(IF(A:A<>"",1,0)) but it only works for single column and I don't have any idea how to use for multiple columns. – KARAN
Does every column A:Z have at least one number or text value in it? – user10852207

1 Answers

3
votes

Using the formula

=MAX(INDEX(($A$1:$Z$2500<>"")*ROW($A$1:$Z$2500),0))

we can get the last filled row of a range. That is the max row number with a filled cell in it.

And using

=MAX(INDEX(($A$1:$Z$2500<>"")*COLUMN($A$1:$Z$2500),0))

we can get the last filled column of a range. That is the max colunmn number with a filled cell in it.

But we need getting the last filled column in the last filled row. So we can using a trick. We do multiplying the row numbers by 10000 and then add up the appropriate column number. So we can get the max of this sums of row number+column number as the last filled column in the last filled row. To get the row number out of this sum, we divide the sum by 10000. To get the column number out of this sum, we take the remainder of this division.

Example:

enter image description here

Formulas: AA1:

=MAX(INDEX(($A$1:$Z$2500<>"")*ROW($A$1:$Z$2500),0))

AA2:

=MAX(INDEX(($A$1:$Z$2500<>"")*COLUMN($A$1:$Z$2500),0))

AA3:

=MAX(INDEX(($A$1:$Z$2500<>"")*(ROW(A1:Z2500)*10000+COLUMN($A$1:$Z$2500)),0))

AA4:

=INDEX($A$1:$Z$2500,MAX(INDEX(($A$1:$Z$2500<>"")*(ROW($A$1:$Z$2500)*10000+COLUMN($A$1:$Z$2500)),0))/10000,MOD(MAX(INDEX(($A$1:$Z$2500<>"")*(ROW($A$1:$Z$2500)*10000+COLUMN($A$1:$Z$2500)),0)),10000))