How To Find The Last Row In an Excel VBA Range

You are adding data on a consistent basis to your spreadsheet, and you need to have a macro that references the last row that was just added to your Excel spreadsheet data range or table.

This article and associated video will show you how to do just that.

Fig 1
excel data range to get last row

So similar to "Fig 1" above you would like to reference row 14 of the table and possibly iterate over each of the cells.

The YouTube video below will take you through the details of how to reference the last row with a vba range object, I will summarise how to do this here, and you can download the Excel vba macro spreadsheet that goes with the tutorial below.

The basics of the solutions are coded below

Line 5 gets a reference to all the data in Fig 1 above.

Line 8 creates a new variable that references the header in spreadsheet row 3

Get the spreadsheet used in the tutorial above.

Line 11 then creates a new variable that references the last row of the range by offsetting the rngHeader variable by the number of rows in the data range (rngData).

The loop that begins on line 10 of the code below creates an iterator (rngCell) that will loop over each cell in the last row, you can use rngCell to do whatever you want to do to each cell in the last row.

01  Sub FormatLastRow()
02    Dim rngHeader As Range, rngdata As Range, rngLastRow As Range    
03
04    ' Substitute the actual worksheet name for "Register"
05    Set rngData = ThisWorkBook.Worksheets("REGISTER").Range("A4").CurrentRegion
06
07    ' Line above gets reference to all of the data
08    Set rngHeader = rngData.Resize(1)    ' Get reference to header row.
09 
10    ' Offset the header reference by the number of rows in the data, less one row to get reference to last row. 
11    Set rngLastRow = rngData.Resize(1).Offset(rngData.Rows.Count - 1)
10    For Each rngCell In rngLastRow
11      ' rngCell iterates over each cell in this last row, 
12      ' substitute your own code for line 14 or download 
13      ' spreadsheet with code to get other ideas. 
14      rngCell.select
15    Next
16
17    ' Clean up object variables
18    Set rngData = Nothing: Set ringleader = Nothing: Set rngLastRow = Nothing
19  End Sub 

In reality I never use select, unless i’m debugging code and want to verify my range references.

If you have any questions about this tutorial, feel free to enter them in the comments section below.

That’s all for now, thanks for reading.

Thanks for reading this post

If you have any queries or comments please leave them below as they help to improve the quality of the information that I provide.

If you found this article useful then don't forget to share it by clicking on one of the social media buttons below.

Leave a Reply

Your email address will not be published. Required fields are marked *