How To Find Last Row using Range.End [Excel VBA]

This tutorial will cover how to find the Last Used Row Using Range.End in Excel VBA.

The Range.End property is synonymous with the Ctrl+Arrow shortcut in Excel. If you use Excel on a regular basis, then this technique is the most intuitive way of grabbing the last row of a data range.

While Ctrl + Down might be the intuitive way to search for the last row, the better way is Ctrl + Up as this considers cases with no data or just the header row.

Find Last Row

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

The equivalent of Ctrl + Up in VBA is Range.End(xlUp). In the example, we will look for the last row in Column A. We need a starting cell to begin our search from. The starting cell will be in the very last Excel row i.e. 1048576, which can be found through code using Rows.Count.

Sub Find_Last_Row()
Dim lrow As Long
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Using Range Object
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
lrow = wsInv.Range("A" & wsInv.Rows.Count).End(xlUp).row
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Using Cell Object
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lrow As Long
lrow = wsInv.Cells(wsInv.Rows.Count, 1).End(xlUp).row
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Using ActiveSheet
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
wsInv.Activate
lrow = Range("A" & Rows.Count).End(xlUp).row
lrow = Cells(Rows.Count, 1).End(xlUp).row
End Sub

Example 1: Identify and Loop Over Data Range

Our data lies within Columns A and J. It starts in Row 1. We will determine the last row using our previous code.

Identify data range using Range object.

Sub Find_Last_Row()
Dim lrow As Long
lrow = wsInv.Range("A" & wsInv.Rows.Count).End(xlUp).row
wsInv.Range("A1:J" & lrow).Select
End Sub

Identify data range using Cell object. This is more dynamic as we will search for the last column as well.

Sub Find_Last_Row()
Dim lrow As Long
lrow = wsInv.Cells(wsInv.Rows.Count, 1).End(xlUp).row
Dim lcol As Long
lcol = wsInv.Cells(1, wsInv.Columns.Count).End(xlToLeft).Column
wsInv.Range(wsInv.Cells(1, 1), wsInv.Cells(lrow, lcol)).Select
End Sub

Using Range variable

Sub Find_Last_Row()
Dim lrow As Long
lrow = wsInv.Range("A" & wsInv.Rows.Count).End(xlUp).row
Dim rng As Range
Set rng = wsInv.Range("A1:J" & lrow)
rng.Select
End Sub

Let’s loop over the data and place the word “Invoice” in every corresponding row in column K.

Sub Find_Last_Row()
Dim lrow As Long
lrow = wsInv.Range("A" & wsInv.Rows.Count).End(xlUp).row
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).

Sub Find_Last_Row()
wsCopyTo.Cells.Clear
lrow = wsInv.Range("A" & wsInv.Rows.Count).End(xlUp).row
wsInv.Range("A1:J" & lrow).Copy
wsInv.Range("A1:J" & lrow).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()
'Find last row of current data range
Dim offrowInv As Long
offrowInv = wsInv.Range("A" & wsInv.Rows.Count).End(xlUp).Offset(1, 0).row
'Find last row of data range of sheet to copy data from
Dim lrowCopyFrom As Long
lrowCopyFrom = wsCopyFrom.Range("A" & wsCopyFrom.Rows.Count).End(xlUp).row
'Copy data
wsCopyFrom.Range("A2:J" & lrowCopyFrom).Copy wsInv.Range("A" & offrowInv)
End Sub