There are many occasions when we want to find the last row or the last column. This post is about the Correct way to find the last row or the last column.
Below mentioned are 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.
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
Rows.Count. We often chose to ignore that. See this question on stackoverflow for 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
Find Last Row in a Sheet
To find the actual 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 error.
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
Similarly we can find the last column as well.
Find Last Column in a Row
To find the last column in Row 1 use this
With Sheets("Sheet1") lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With
Find Last Column in a Sheet
To find the actual last column in the sheet, use this.
With Sheets("Sheet1") If Application.WorksheetFunction.CountA(.Cells) = 0 Then lastCol = .Cells.Find(What:="*", _ After:=.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Else lastCol = 1 End If End With