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.