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.

  1. UsedRange
  2. xlDown
  3. 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 UsedRangexlDown 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 stackoverflow for 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.

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