Clever Geek Handbook

Find the last used cell in the block of cells

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

  • Find the last column (rightmost) containing non-null data
  • Find the bottom cell in this column that contains non-null data
  • Returns the address of this cell

For example, in block B2: I16 :

Spreadsheet

The function should return: I15 , not 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

How a worksheet should work in a macro-free environment.

+3


source to share


3 answers


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)

Click Ctrl+ Shift+Enter

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

In the picture below, it was applied on a range L1:P5

and gave the correct result O4

.



enter image description here

The only drawback I've found so far is that it will fail if the range contains cells with errors, but from reading the OP's UDF, this doesn't seem to be a problem. If this happens, some additional ones IFERROR

will fix this problem:

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

Click Ctrl+ Shift+Enter

EDIT: Added a parameter 4

to the function ADDRESS

to remove $

from the result. My test results are the same as OP UDF after I changed it r(j, i)

to r.Parent.Cells(j, i)

.

+3


source


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


enter image description here

+3


source


My first approach was like @ 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 is AGGREGATE

used to compute the maximum:
COL_NUM*10^7+ROW_NUM

for nonblank cells (multiplying by 10 ^ 7 gives the column priority). So this function technically returns both coordinates (for example, for P4

this 160000004

is the 16th column and the 4th row). MOD

retrieves the line number.

But if one AGGREGATE

can return both coordinates (as one number), the next step was to try and find a formula to return the address using AGGREGATE

only once. This is the best I could think of:

=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:

  • decodes column letters from Base36 (shifted 6 digits from the left) to decimal
  • decodes line number (!) from Base36 to decimal
  • calculates the maximum for nonblank cells
  • encodes the result as Base36

Disadvantages:

  • BASE

    was introduced in Excel2013
  • The return formula P000004

    instead of P4

    - but still a valid cell address - can be used withINDIRECT

  • Performs a lot of calculations - this is just an attempt to solve a problem with just one AGGREGATE

    .
+1


source






More articles:


All Articles