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.

1. Using Range.End Method

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

2. Using UsedRange Property

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)

Limitation of UsedRange method: This is not applicable when there are some blank rows because Rows.count stops at the first empty cell which is nothing but the gap within the data set.

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.

4. Using Special Cells Method

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

Important Note: While looking for the last used cell '.SpecialCell' also considers formatting, empty cells, and cells with formulas.

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.

Limitation Of COUNTA Function : In case the data is discontinuous(i.e. having empty rows or columns) the result will be the number of non empty rows but what we are looking for is the row corresponding to the last entry.

Let's take an example to understand -

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

COUNTA only works when the data is continuous i.e. without any gap.

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.

VBA : Calculating Sum using COUNTA Function

6. Using Current Region

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.

This method also fails in the case of non continuous or scattered data.

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.

Here Range("A1") acts as a reference cell around which ".CurrentRegion" will find the range filled with data. Rows.Count will tell us the total number of rows in that range.

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.

Practical Example : How to Copy Data to Another Sheet

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

Important Note :

.Copy: It copies the data from the reference range.
.PasteSpecial Paste:=xlPasteValues: paste only the values and ignores the formula or formatting that has been applied to the source.

About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Let's Get Connected Email LinkedIn