Get the last row of a column in Excel, without VBA

Question

In general, getting the last row of a given column in Excel is easily done with a User-Defined Function in VBA:

Function lastRow(wsName As String, Optional columnToCheck As Long = 1) As Long
    Dim ws As Worksheet
    Set ws = Worksheets(wsName)
    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row
End Function

For the last row there are two excel formulas I found so far:

=SUMPRODUCT(MAX((A:A<>"")*ROW(A:A)))
=MAX(IFERROR(MATCH(E1+99,A:A),0),IFERROR(MATCH("zzz",A:A),0))

The problem with them is that the first does not work, if there is an error in the range and the second does not return the real last row, if it is an error, but the last non-error row, thus returning 6 and not 7:

The question is how to get a formula that returns 7, as this is the last row in column A that is used? Although, it is an error. Without VBA.

=MAX(IFERROR(A:A<>"",1)*ROW(A:A)) but it is an array formula. — Scott Craner
@ScottCraner - yes, it seems it works quite well. Any chance to avoid the ctrl+shift part? — Vityata
Which is why I put it in a comment, did not think you wanted that need. But looks like you got an answer. — Scott Craner

Pᴇʜ Pᴇʜ · Accepted Answer · 2018-10-18T13:59:52

This one (without an array formula)

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A))

How does it work?

NOT(ISBLANK(A:A)) returns an array of True and False
1/(NOT(ISBLANK(A:A))) then 1 divided by that array results in another array of 1 or #DIV/0! which is used as lookup_vector
We use 2 as lookup_value which cannot be found because the largest value in the lookup_array is 1, so lookup will match the last 1 in the array.
As result_vector we use ROW(A:A) to get the row number of the last used cell
(alternatively use A:A to get the value instead of the row number).

Get the last row of a column in Excel, without VBA

2 Answers