vba

Oftentimes you need to get the last row of a dataset so can do some manipulation on it. And while `Cells(Rows.Count, "A").End(xlUp).Row` is just one line, it's hard to remember. And yes, you can look it up each time but if you find yourself doing that multiple times a day, you can simply encapsulate that line in a function.

Function GetLastRow() as Long

    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    GetLastRow = lastRow

End Function

Then it's a simple operation to call it up.

Dim lastRow as Long
lastRow = GetLastRow

Let's extend it a bit to handle some edge cases. You dataset may be in another worksheet and you furthest data point may not be in column "A." We will add some optional parameters to handle these values. The complete function is as follows:

Function GetLastRow(Optional ws As Worksheet, Optional col As Variant) As Long

    If ws Is Nothing Then Set ws = ActiveSheet
    If IsMissing(col) Then col = "A"

    Dim lastRow As Long
    lastRow = ws.Cells(Rows.Count, col).End(xlUp).Row
    
    GetLastRow = lastRow

End Function

If you don't set the params, they default to active sheet and column "A." The col param, being a Variant, can take a number or a string. The following function calls are all valid.

x = GetLastRow(, 1)
x = GetLastRow(, "A")
x = GetLastRow(ActiveSheet)
x = GetLastRow

I have found this function extremely useful and I keep it in my personal VBA library. My personal library will be the subject of coming posts, and I will post the complete library on GitHub, but for now we are going back to Snek. Stay tuned.