VBA : Multiple Ways To Find Last Used Row

This tutorial explains several ways to find the last used row in MS Excel using VBA. Some of the common methods to find the last used row are discussed below.

The Range.End method returns the last non-empty cell in a range.

Sub lastNonEmptyRow()
     Dim lastRow As Long
     
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    MsgBox "The last row in column is " & lastRow

  End Sub
  

Press Run or F5 to run the above code.
The result looks like this.

Rows.Count corresponds to the total number of rows. Hence Range(“A” & Rows.Count) will give the last cell in column A..End(xlUp) starts searching from the last cell in the upward direction and eventually stops at the first encountered non-empty cell. .Rows simply returns the row number associated with the encountered non empty cell.

Calculating Sum using Range.End method

The following VBA code shows how to calculate the sum of a range using the Range.End method.

Sub FindingSum()
    Dim lastRow As Long
    With ActiveSheet
       
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
    
        .Range("D4").Formula = "=SUM(C2:C" & lastRow & ")"
    End With
End Sub 

UsedRange property defines the range in which the data is present. It may include the cells from which data has been deleted and the cells on which formatting has been applied.

Sub last row()
Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Rows.Count
MsgBox "The last row in column is " & lastRow
End Sub

Press Run or F5 to run the above code.
The result looks like this

Object defines the object on which usedRange property is applied. For example- ActiveSheet or Worksheet(“name”). UsedRange returns the range in which the data is present. Rows.Count counts the number of rows present in the selected range(Object.UsedRange)

Calculating Sum using UsedRange method

The following VBA code shows how to calculate the sum of a range using the UsedRange method.

Sub FindingSum()
    Dim lastRow As Long
    With ActiveSheet
         lastRow = ActiveSheet.UsedRange.Rows.Count
        .Range("D4").Formula = "=SUM(C2:C" & lastRow & ")"
    End With
End Sub

3. Using Find method

The Find method can be used to search for the last non-empty cell in a column. Let’s try to find out the last row in a column A.

Sub FindLastRow()
    Dim lastRow As Long
    Dim lastCell As Range

      Set lastCell = ActiveSheet.Columns("A").Find(What:="*", SearchDirection:=xlPrevious, LookIn:=xlValues)
      
    If Not lastCell Is Nothing Then
        lastRow = lastCell.Row
             MsgBox "The last row is " & lastRow
    Else
             MsgBox "No data found in column A"
    End If
End Sub 

The Result looks like this

Object defines the object on which the Find method will work. For example- Column(A).
Find searches for the value that will be prompted for search (“*” means any type of value.). xlPrevious searches from bottom to up. xlValues looks for any value such as numeric, string,etc.

Calculating Sum using Find Method

Sub FindingSum()
    Dim lastRow As Long
    Dim lastCell As Range
    
      Set lastCell = ActiveSheet.Columns("A").Find(What:="*", SearchDirection:=xlPrevious, LookIn:=xlValues)
          lastRow = lastCell.Row
         ActiveSheet.Range("D4").Formula = "=SUM(C2:C" & lastRow & ")"

End Sub

Press Run or F5 to run the above code.

The SpecialCells(xlCellTypeLastCell) is one of the simplest way to find the last used row as it gives us the last used cell on a worksheet.

Sub FindLastRowWithSpecialCells()
Dim lastRow As Long
lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
MsgBox "Last used row is " & lastRow
End Sub

.SpecialCells looks for a particular type of cell. xlCellTypeLastCell identifies the last cell used in a data set and .Rows returns the row to which the last cell belongs

Finding sum by using last row

Sub FindingSum()
    Dim lastRow As Long
    
         lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
         ActiveSheet.Range("D4").Formula = "=SUM(C2:C" & lastRow & ")"

End Sub

5. Using COUNTA Function

COUNTA function returns the number of non-blank cells in a range. It includes text, numbers and formulas.

Sub lastNonEmptyRow()

    Dim lastNonEmptyRow As Long
    lastNonEmptyRow = Application.WorksheetFunction.COUNTA(Range("A:A"))

    MsgBox "The last non-empty row in column is " & lastNonEmptyRow

End Sub

Press Run or F5 to run the above code.
The result looks like this.

Here we use Application.WorkSheetFunction because COUNTA is an inbuilt function in excel. COUNTA counts only the number of non empty cells but not the total no.of rows.

Here actually data consists of some empty rows, hence COUNTtA actually count the total no.of non-empty cell in that column.
In the above scenarion COUNTA method fails.

Calculating Sum using COUNTA Function

Sub FindingSum()

    Dim lastNonEmptyRow As Long
    lastNonEmptyRow = Application.WorksheetFunction.CountA(Range("A:A"))

     ActiveSheet.Range("D4").Formula = "=SUM(C2:C" & lastNonEmptyRow & ")"


End Sub

Press Run or F5 to run the above code.

Current region method covers all the data present in four directions(Up, Down, Right, Left) around the reference cell. It will stop at a point where it encounters the first empty cell.

Sub lastNonEmptyRow()
   Dim lastRow As Long
   lastRow = Range("A1").CurrentRegion.Rows.Count
    MsgBox "The last non-empty row in column is " & lastRow
End Sub

Press Run or F5 to run the above code.

Finding sum by using last row

Sub FindingSum()

   Dim lastRow As Long
   lastRow = Range("A1").CurrentRegion.Rows.Count
   ActiveSheet.Range("D4").Formula = "=SUM(C2:C" & lastRow & ")"


End Sub

Press Run or F5 to run the above code.

In this section we will discuss about how to copy data from one sheet to another with the help of a sample data.

Sub CopyDataToAnotherSheet()
  Dim lastRow As Long
  
    lastRow = ActiveSheet.UsedRange.Rows.Count
    Sheets("Sheet1").Range("H1:J" & lastRow).Copy
    Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
End Sub

Press Run or F5 to run this code.
The Result will be

Read MoreListenData