Formula to get the index of the row with the last non-empty cell in a 2D array

Question

To compute some checks on the data entered, I need to get the index of the row which has the last non empty cell in a 2D array. Each row contains a single non-empty cell.

To solve this, the approach I went for is to first compute the column index of the non-empty cell for each row with =MATCH(FALSE;ISBLANK($A1:$Z1);0). Then I take the index of the max of this using the MATCH function again with a large number as the first argument, and mode 1 as the third. However I don't know what to use as the second argument.

When doing the formula using multiple cells, I can simply put the first MATCH function at the end of each row, then in another cell there is the second MATCH call applied on the rows. However, I need to do this in a single cell. It is not a problem if the function is very long (I only have 5 rows), but I can't find how to apply the MATCH function on an "inline list", so I could just copy the first MATCH call for each row to create an inline list or "virtual array", and apply the second MATCH to it.

I tried something like =MATCH(99;{AA1;AA2;AA3;AA4;AA5};1) (with the cell references being replaced by the first MATCH formula). But apparently Excel doesn't support {..;..} inline array. Am I wrong ?

How would you solve this issue with this approach or another ?

Is it to give row index of right most empty cell in given range? — user11222393
– user11222393, Commented Dec 1, 2023 at 13:04

Mayukh Bhattacharya · Accepted Answer · 2023-12-01 12:58:11Z

1

Perhaps for row index . The array ROW(A1:E11)/(A1:E11="") divides each row by 1 for the rows and returns the corresponding cells where in the range is not empty, the overall procedure gives the max value of row numbers from the resultant array.

=AGGREGATE(14;6;ROW(A1:E11)/(A1:E11="");1)

answered Dec 1, 2023 at 12:58

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Black cat · Accepted Answer · 2023-12-01 12:28:11Z

0

With this formula returns the last row where the range has a non blank cell.

=MAX(NOT(ISBLANK(A1:F100))*ROW(1:100))

Take care of not to put it in the range where you want to find the last row. (Circular reference)

answered Dec 1, 2023 at 12:28

Black cat

7,6358 gold badges68 silver badges99 bronze badges

Comments

user11222393 · Accepted Answer · 2023-12-01 13:03:26Z

0

To get relative row index you may try:

=MAX(LET(z,A6:F10,b,IF(z="",COLUMN(z)),IF(b=MAX(b),ROW(z)-ROW(INDEX(z,1,1))+1)))

Result:

To get absolute row index:

=MAX(LET(z,A6:F10,b,IF(z="",COLUMN(z)),IF(b=MAX(b),ROW(z))))

You may need to change , to ; in formulas due to your regional settings.

edited Dec 1, 2023 at 13:03

answered Dec 1, 2023 at 11:59

user11222393

5,6494 gold badges17 silver badges28 bronze badges

Comments

Ron Rosenfeld · Accepted Answer · 2023-12-02 13:18:00Z

One problem with the offered solutions is that for large arrays, the computation may take a long time, or (at least on this machine with 32-bit Excel) run out of resources.

I suggest a VBA solution.

The UDF below will return the address of the cell in the last used row on the current or any specified worksheet.
It can be easily modified to give the appropriate row number, either an absolute number on the worksheet, or the relative number within the defined array range, depending on what you really require. To enter this User Defined Function (UDF),
alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like =lastRow(A:Z) in some cell.

Note that this UDF can be placed anyplace on the worksheet and will not have the circular reference issue of other formulas.

Option Explicit
'enter either worksheet as a string, or range as a range object
Function LastRow(SheetOrRange) As String
    Application.Volatile 'else it won't update when changes made
    Dim r As Range, c As Range
    
Select Case TypeName(SheetOrRange)
    Case "Range"
        Set r = SheetOrRange
    Case "String"
        Set r = Worksheets(SheetOrRange).Cells
    Case Else
        MsgBox "unexpected error"
        Exit Function
End Select
    
    
With r
    Set c = .Find(what:="*", after:=.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious)
    If Not c Is Nothing Then
        LastRow = c.Address
    Else
        LastRow = xlErrValue
    End If
End With

End Function

Collectives™ on Stack Overflow

Formula to get the index of the row with the last non-empty cell in a 2D array

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related