Getting the last row of a given column in Excel is easily done with a User-Defined Function in VBA:
|
1 2 3 4 5 |
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:
|
1 2 |
=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:
|
1 |
=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
|
1 |
=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A)),0) |
Last Column of the first row
|
1 |
=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(1:1))),COLUMN(1:1)),0) |
To get the values
Last Row Value of Column A
|
1 |
=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A) |
Last Column Value of the first row
|
1 |
=LOOKUP(2,1/(NOT(ISBLANK(1:1))),1:1) |