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

问题:

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.

回答1:

This one (without an array formula)

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

How does it work?

1. NOT(ISBLANK(A:A)) returns an array of True and False
2. 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
3. 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.
4. 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).

回答2:

Try the following formula:

=SUMPRODUCT((MAX(IFERROR(((A:A)<>"");TRUE)*ROW(A:A))))

Remember that this formula needs to be entered with control+shift+enter.

(A:A) <> "" : checks for not empty cells and returns TRUE if not empty.
(IFERROR(((A:A)<>"");TRUE) : checks for not empty cells and returns TRUE if not empty, if an error is detected it returns also TRUE.

Rest of code works as before.

More info about the control+shift+enter combination can be found here: Guidelines and examples of array formulas