VBA – Retrieve Last Row From More Than One Column
Normally, it’s enough to know the last row in a specific column. In those cases I normally just use this widely method:
'Getting last row from column A LastRow = Range("A" & Rows.Count).End(xlUp).row
But when you have several columns (for example 1 to 10) and you need to retrieve the last cell in use in any of these columns, we need something else. A big thanks to Jan Brun Rasmussen for having provided the following brilliant solution:
Sub DefineRange() Dim StartColumn As Integer Dim EndColumn As Integer Dim LastRow 'define first and last column of the range StartColumn = 2 EndColumn = 10 LastRow = FindLastRow(StartColumn, EndColumn) 'call the function FindLastRow and return the value to the variable LastRow MsgBox "Last row is " & LastRow 'show and tell End Sub '---the above sub calls the function below, providing the function with the range parameters. Function FindLastRow(iColI As Integer, iColN As Integer) As Long Dim iRowN As Long Dim iRowI As Long 'Loop thorugh the columns For i = iColI To iColN 'Define each columns' last row iRowI = Cells(Rows.Count, i).End(xlUp).Row 'if last row is larger than in the previous column, save row number to iRowI If iRowI > iRowN Then iRowN = iRowI Next i FindLastRow = iRowN End Function
M | T | W | T | F | S | S |
---|---|---|---|---|---|---|
1 | 2 | 3 | ||||
4 | 5 | 6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 | 16 | 17 |
18 | 19 | 20 | 21 | 22 | 23 | 24 |
25 | 26 | 27 | 28 | 29 | 30 | 31 |
Recent Posts
- VBA – Import CSV file
- VBA – Get name of file without extension
- UserForm Listbox – Populate list and extract selected items
- VBA – Retrieve Last Row From More Than One Column
- VBA – Check Extension of File
- VBA – Delete PivotTables
- VBA – Add New WorkSheet After The Last Worksheet
- VBA – Toggle Between Open Excel Files
- VBA – Looping through all files in a folder
- VBA – Create and add items to dynamic arrays
- VBA – Loop through arrays
- Excel formula – Miscellaneous
- VBA – Delete all files in a folder
- VBA – Loop through sheets
- VBA – Define PageSetup (PaperSize, Orientation, etc.) and Print Excel Sheet