Last Used Row (Last Used Column) in Excel with Formula (without VBA)
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
There are two other formulas, which would get the last used cell in a given row, depending on whether it is a numeric or string:
=IFERROR(MATCH(E1+99,A:A),0)
=IFERROR(MATCH("zzz",A:A),0)
The first one gets the last used numeric row and the second one returns the last used non-numeric row. For last used numeric/non-numeric row, a union of these could be used:
=MAX(IFERROR(MATCH(E1+99,A:A),0),IFERROR(MATCH("zzz",A:A),0))
However, there is a small problem – when the last cell is an error, the MAX() formula returns not the last one. In the example below it returns 6 and not 7 :
Thus, the four ultimate formulas, that always work for last “things” are the following:
To get the rows/columns:
Last Row of column A
=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A)),0)
Last Column of the first row
=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(1:1))),COLUMN(1:1)),0)
To get the values
Last Row Value of Column A
=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)
Last Column Value of the first row
=LOOKUP(2,1/(NOT(ISBLANK(1:1))),1:1)