VBA – Tips & Tricks

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