How To Find Last Row using CurrentRegion and UsedRange [Excel VBA]

This tutorial will cover how to find the Last Used Row Using CurrentRegion and UsedRange using Excel VBA.

For a data set, the current region is the entire range of data that is joined by continuous rows and continuous columns. Looking outside in, the current region is the entire range of data that is bound by blank rows and blank columns. Keyboard shortcut to select the CurrentRegion is Ctrl + A.

CurrentRegion offers a quick and easy way to select a data range.

However, if our data is separated by blank rows or columns, CurrentRegion won’t be able to select the entire range. For this scenario, we need to use the UsedRange property of the worksheet object.

Find Last Row using CurrentRegion

Note: In all examples, the worksheet code name wsInv is being used. You could refer to the worksheet directly instead: ThisWorkbook.Sheets(“Invoice”)

CurrentRegion is a property of the range object. If our data starts in Row 1, we can just count the number of rows in the data range. But, if the data starts after row 1, then we need to grab the row number of the last row using the row index of the last row.

Sub Last_Row()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'If data starts from row 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lrow As Long
lrow = wsInv.Range("A1").CurrentRegion.Rows.Count
Debug.Print lrow
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'If data starts from a row other than the first row. 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lrow As Long
lrow = wsInv.Range("A2").CurrentRegion.Rows(wsInv.Range("A2").CurrentRegion.Rows.Count).row
Debug.Print lrow
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'We can simply this by feeding the CurrentRegion into a range object
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim rng As Range
Set rng = wsInv.Range("A2").CurrentRegion
lrow = rng.Rows(rng.Rows.Count).row
Debug.Print lrow
End Sub

Example 1: Identify and Loop Over Data Range

CurrentRegion defines the range of used data. In the below example, we will iterate over the range of data and add the word “Invoice” in the corresponding row in Col K.

Sub Last_Row()
Dim lrow As Long
lrow = wsInv.Range("A1").CurrentRegion.Rows.Count
Dim i As Long
For i = 2 To lrow
   wsInv.Range("K" & i).Value = "Invoice"
Next i
End Sub

Example 2: Copy Data Range to New Sheet

We will copy data from current sheet wsInv onto sheet: CopyTo (code name: wsCopyTo). If you want to copy just a specific column, we can identify the last row and copy the used data within that column. But, if you want to copy the entire range, we can copy the CurrentRegion directly.

Sub Find_Last_Row()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Copy by identifying the last row
    'Use to copy specific columns
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
wsCopyTo.Cells.Clear
Dim lrow As LoadPictureConstants
lrow = wsInv.Range("A1").CurrentRegion.Rows.Count
wsInv.Range("A1:J" & lrow).Copy wsCopyTo.Range("A1")
wsCopyTo.Columns.AutoFit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Copy directly using current region (Recommended)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
wsCopyTo.Cells.Clear
wsInv.Range("A1").CurrentRegion.Copy wsCopyTo.Range("A1")
wsCopyTo.Columns.AutoFit
End Sub

Example 3: Paste Data at Bottom of Current Data Range

We will copy data from sheet: CopyFrom (code name: wsCopyFrom)and paste it at bottom of our data range in wsInv sheet.

Sub Find_Last_Row()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Identify first unused row in our original data range
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim offrowInv As Long
offrowInv = wsInv.Range("A1").CurrentRegion.Rows.Count + 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Feed the entire data that we want to copy, into a range object
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim rngCopyFrom As Range
Set rngCopyFrom = wsCopyFrom.Range("A1").CurrentRegion
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'If the data has headers, resize the range to exclude it
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set rngCopyFrom = rngCopyFrom.Offset(1, 0).Resize(rngCopyFrom.Rows.Count - 1, rngCopyFrom.Columns.Count)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Copy the data over
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
rngCopyFrom.Copy wsInv.Range("A" & offrowInv)
End Sub

Worksheet.UsedRange

If our data is not continuous i.e. there are blank rows or columns in between our dataset, then we need to use the UsedRange property of the Worksheet object.

Sub Last_Row()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'If data starts from row 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lrow As Long
lrow = wsInv.UsedRange.Rows.Count
Debug.Print lrow
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'If data starts from a row other than the first row.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lrow As Long
lrow = wsInv.UsedRange.Rows(wsInv.UsedRange.Rows.Count).row
Debug.Print lrow
End Sub

Use below code to delete blank rows in a worksheet within our data range. The code will find the last used row using UsedRange and then loop backwards till row 1, deleting blank rows as the loop progresses.

Sub Last_Row()
Dim i As Long
For i = wsInv.UsedRange.Rows(wsInv.UsedRange.Rows.Count).row To 1 Step -1
    If WorksheetFunction.CountA(wsInv.Rows(i)) = 0 Then
        wsInv.Rows(i).Delete
    End If
Next i
End Sub

Related Posts