Question
When I want to find the last used cell value, I use:
Dim LastRow As Long
LastRow = Range("E4:E48").End(xlDown).Row
Debug.Print LastRow
I'm getting the wrong output when I put a single element into a cell. But when I put more than one value into the cell, the output is correct. What's the reason behind this?
Answer
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](https://stackoverflow.com/questions/19985345/vba-excel-
compability-mode#comment29753337_19985345) 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
