**NOTE**: I intend to make this a “one stop post” where you can use the `Correct`

way to find the last row. This will also cover the best practices to follow when finding the last row. And hence I will keep on updating it whenever I come across a new scenario/information.

**Unreliable ways of finding the last row**

Some of the most common ways of finding last row which are highly unreliable and hence should never be used.

- UsedRange
- xlDown
- CountA

`UsedRange`

should **NEVER** be used to find the last cell which has data. It is highly unreliable. Try this experiment.

Type something in cell `A5`

. Now when you calculate the last row with any of the methods given below, it will give you 5. Now color the cell `A10`

red. If you now use the any of the below code, you will still get 5. If you use `Usedrange.Rows.Count`

what do you get? It won’t be 5.

Here is a scenario to show how `UsedRange`

works.

`xlDown`

is equally unreliable.

Consider this code

```
lastrow = Range("A1").End(xlDown).Row
```

What would happen if there was only one cell (`A1`

) which had data? You will end up reaching the last row in the worksheet! It’s like selecting cell `A1`

and then pressing `End` key and then pressing `Down Arrow` key. This will also give you unreliable results if there are blank cells in a range.

`CountA`

is also unreliable because it will give you incorrect result if there are blank cells in between.

And hence one should avoid the use of `UsedRange`

, `xlDown`

and `CountA`

to find the last cell.

## Find Last Row in a Column

To find the last Row in Col E use this

```
With Sheets("Sheet1")
LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With
```

If you notice that we have a `.`

before `Rows.Count`

. We often chose to ignore that. See THIS question on the possible error that you may get. I always advise using `.`

before `Rows.Count`

and `Columns.Count`

. That question is a classic scenario where the code will fail because the `Rows.Count`

returns `65536`

for Excel 2003 and earlier and `1048576`

for Excel 2007 and later. Similarly `Columns.Count`

returns `256`

and `16384`

, respectively.

The above fact that Excel 2007+ has `1048576`

rows also emphasizes on the fact that we should always declare the variable which will hold the row value as `Long`

instead of `Integer`

else you will get an `Overflow`

error.

Note that this approach will skip any hidden rows. Looking back at my screenshot above for column A, if row 8 were hidden, this approach would return `5`

instead of `8`

.

## Find Last Row in a Sheet

To find the `Effective`

last row in the sheet, use this. Notice the use of `Application.WorksheetFunction.CountA(.Cells)`

. This is required because if there are no cells with data in the worksheet then `.Find`

will give you `Run Time Error 91: Object Variable or With block variable not set`

```
With Sheets("Sheet1")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lastrow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lastrow = 1
End If
End With
```

## Find Last Row in a Table (ListObject)

The same principles apply, for example to get the last row in the third column of a table:

```
Sub FindLastRowInExcelTableColAandB()
Dim lastRow As Long
Dim ws As Worksheet, tbl as ListObject
Set ws = Sheets("Sheet1") 'Modify as needed
'Assuming the name of the table is "Table1", modify as needed
Set tbl = ws.ListObjects("Table1")
With tbl.ListColumns(3).Range
lastrow = .Find(What:="*", _
After:=.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End With
End Sub
```