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.

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

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.

Disclosure:- If you buy this or any book using this link (at the moment amazon.com only). Amazon may give me a small commission (at no extra cost to you). And if you do get one, many thanks.

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.

Wanna grab me a coffee? – Then Click This Green Box

If you find this article or videos really helpful, then consider grabbing me a coffee, it takes a lot of work and expense to keep this site going, the more caffine I have the harder I can work.

And if you do grab me a coffee (just click anywhere on this green area), thanks for your support 😉