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.
Some of the most common ways of finding last row which are highly unreliable and hence should never be used.
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
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
CountA to find the last cell.
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
Rows.Count. We often chose to ignore that. See THIS question on the possible error that you may get. I always advise using
Columns.Count. That question is a classic scenario where the code will fail because the
65536 for Excel 2003 and earlier and
1048576 for Excel 2007 and later. Similarly
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
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
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
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
~ Answered on 2012-06-23 13:33:16