Find last used cell in Excel VBA
cell, excel, excel-formula, vba
Solution
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
Problem
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?