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) |