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.
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.