Locate the last used cell in a block of cells

Question

I need a formula to locate the last used cell in a block. By last used cell I mean:

1. Locate the last column (right-most) containing non-null data
2. Locate the lowest cell in that column that contains non-null data
3. Return the address of that cell

For example, in the block B2:I16:

The function should return: I15 rather than D16. I already have a VBA UDF that I am trying to replace:

Public Function FindLast(r As Range) As String
    Dim nLastRow As Long, nLastColumn As Long
    Dim nFirstRow As Long, nFirstColumn As Long
    Dim i As Long, j As Long

    nLastRow = r.Rows.Count + r.Row - 1
    nLastColumn = r.Columns.Count + r.Column - 1
    nFirstRow = r.Row
    nFirstColumn = r.Column

    For i = nLastColumn To nFirstColumn Step -1
        For j = nLastRow To nFirstRow Step -1
            If Len(r(j, i)) > 0 Then
                FindLast = r(j, i).Address(0, 0)
                Exit Function
            End If
        Next j
    Next i
End Function

As the worksheet must work in a macro-free environment.

Answer 1

This {array formula} works:

=ADDRESS(MAX(ROW(L1:P5)*(LEN(L1:P5)>0)*(COLUMN(L1:P5)=
   MAX(COLUMN(L1:P5)*(LEN(L1:P5)>0)))),
   MAX(COLUMN(L1:P5)*(LEN(L1:P5)>0)), 4)

Press Ctrl + Shift + Enter

Obviously the second term catches the correct column (which is the easy part). The first term includes the second term in it, in order to search that column for the last populated row.

In the figure below it was applied on the range L1:P5 and yielded the correct result O4.

The only shortcoming I found so far is that it will error out if the range contains error cells, but from reading the OP's UDF it doesn't seem to be an issue. If it does, some additional IFERROR will solve it:

=ADDRESS(MAX(ROW(L1:P5)*IFERROR(LEN(L1:P5)>0, 0)*(COLUMN(L1:P5)=
  MAX(COLUMN(L1:P5)*IFERROR(LEN(L1:P5)>0,0)))),
  MAX(COLUMN(L1:P5)*IFERROR(LEN(L1:P5)>0, 0)), 4)

Press Ctrl + Shift + Enter

EDIT: Added parameter 4 to the ADDRESS function to remove the $ from the result. The results in my tests match the OP's UDF after I modified it replacing r(j, i) with r.Parent.Cells(j, i).

Answer 2

Here is a non CSE version:

=ADDRESS(AGGREGATE(14,6,(ROW(INDEX(L1:P5,0,AGGREGATE(14,6,COLUMN(L1:P5)/(L1:P5<>""),1)-MIN(COLUMN(L1:P5))+1)))/(INDEX(L1:P5,0,AGGREGATE(14,6,COLUMN(L1:P5)/(L1:P5<>""),1)-MIN(COLUMN(L1:P5))+1)<>""),1),AGGREGATE(14,6,COLUMN(L1:P5)/(L1:P5<>""),1))

Answer 3

My first approach was similar to @ScottCraner's.

=ADDRESS(MOD(AGGREGATE(14,6,(ROW(L1:P5)+COLUMN(L1:P5)*10^7)*(L1:P5<>""),1),10^7),AGGREGATE(14,6,COLUMN(L1:P5)*(L1:P5<>""),1),4)

Here the first AGGREGATE is used to calculate maximum of:
COL_NUM*10^7+ROW_NUM for nonempty cells (multiplication by 10^7 ensures column precedence). So this function technically returns both coordinates (e.g. for P4 it is 160000004 - 16th column and 4th row). MOD extracts row number.

But if one AGGREGATE can return both coordinates (as one number), the next step was to try find a formula to return the address using AGGREGATE only once. This is the best I could come up with:

=BASE(AGGREGATE(14,6,(DECIMAL(ROW(L1:P5),36)+36^6*(DECIMAL(ADDRESS(1,COLUMN(L1:P5),4),36)-1)*(L1:P5<>"")),1),36)

This formula:

1. decodes column letters from Base36 (shifted 6 digits left) to decimal
2. decodes row number(!) from Base36 to decimal
3. calculates maximum for nonempty cells
4. encodes result as Base36

Drawbacks:

1. BASE was introduced in Excel2013
2. Formula return P000004 instead of P4 - but it is still valid cell address - can be use with INDIRECT
3. Performs a lot of calculations - it is only an attempt to solve the problem with one AGGREGATE only.