Skip to Content

Ways to find last row using VBA


Determining the last row of data in a worksheet is a common task when working with dynamic data ranges. Here are the most reliable methods to identify the last row, along with explanations and use cases for each.


1. Using Cells.SpecialCells(xlCellTypeLastCell)

Dim lastRow As Long lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row MsgBox "Last row is: " & lastRow

Explanation:

  • SpecialCells(xlCellTypeLastCell): Identifies the last used cell in the worksheet.
  • Use case: When you need the last row based on any cell usage (data or formatting).
  • Limitations: Can overestimate the last row if unused cells were previously formatted.


2. Using Range.End(xlUp)

Dim lastRow As Long lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row MsgBox "Last row with data in column A is: " & lastRow

Explanation:

  • Starts at the bottom of the specified column (Rows.Count, "A") and moves up until it finds data.
  • Use case: To find the last row of data in a specific column (e.g., column A).
  • Advantages: Ignores blank rows and unused cells; reliable for single-column operations.


3. Using WorksheetFunction.CountA

Dim lastRow As Long lastRow = WorksheetFunction.CountA(ActiveSheet.Columns("A")) MsgBox "Last row with any data in column A is: " & lastRow

Explanation:

  • Counts the non-empty cells in a specified column.
  • Use case: Works well when data is contiguous and all rows are filled.


4. Using UsedRange.Rows.Count

Dim lastRow As Long lastRow = ActiveSheet.UsedRange.Rows.Count MsgBox "Last row in the used range is: " & lastRow

Explanation:

  • The UsedRange property identifies the range of cells with data.
  • Adjusts for cases where the range doesn’t start in row 1.
  • Use case: When you need to consider the entire worksheet, including headers and empty rows in between.


5. Using a Loop (Manual Iteration)

Dim lastRow As Long Dim cell As Range For Each cell In ActiveSheet.Columns("A").Cells If IsEmpty(cell.Value) Then lastRow = cell.Row - 1 Exit For End If Next cell MsgBox "Last row is: " & lastRow

Explanation:

  • Loops through cells in a column to manually find the last non-empty cell.
  • Use case: Rarely needed, but useful for complex custom logic.
  • Limitations: Slower for large datasets.


6. Using Find Method

Dim lastRow As Long With ActiveSheet If Not .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious) Is Nothing Then lastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Else lastRow = 1 ' Default to 1 if the sheet is empty End If End With MsgBox "Last row with data is: " & lastRow

Explanation:

  • Searches for the last used cell in the worksheet.
  • Use case: Highly accurate for detecting non-empty cells.


Comparison of Methods:

MethodSpeedUse CaseAccuracy
SpecialCellsFastDetect the last used cell (data or formatting).Moderate (includes formatting).
Range.End(xlUp)FastIdentify the last data row in a specific column.High
WorksheetFunction.CountAFastContiguous datasets without blank rows.High
UsedRangeModerateDetect data in the entire used range.Moderate
LoopSlowCustom logic for row evaluation.High
FindFastDetect the last row with any data.Very High


Best Practice:

  • Use Range.End(xlUp) for specific columns with clear data.
  • Use Find when dealing with non-contiguous datasets.
  • Avoid SpecialCells for critical calculations due to potential inaccuracies from formatting.



in VBA
RKsTechAdemy 6 December 2024
Share this post
Our blogs
Archive
Sign in to leave a comment
Check if a File Exists using VBA