Sub vs Function to pass variable or parameter

[koehlerc14]

I've created a few lines of code that works great and I use to determine the last cell of data in excel. The code works great, but I'm having to write it every time I want to use in my code. Ideally, I would be able to call the function and return the parameters to use as values in subsequent code. On the active worksheet, I could call the code, use values within a range to create a table and for example filter out particular rows.

As an amateur coder, any advice on best practices would be greatly appreciated!

Sub Display_LastCell()
'Determine last cell with data
'Display the last cell with data

Dim nrow As Long

nrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ncol = ActiveSheet.UsedRange.Columns.Count
lcol = Split(Cells(1, ncol).Address(True, False), "$")
lcol = lcol(0)

MsgBox "Last Cell is " & lcol & nrow

End Sub



Private Function LastCell()
'Function finding the last cell in active worksheet with data
    
    nrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    ncol = ActiveSheet.UsedRange.Columns.Count
    lcol = Split(Cells(1, ncol).Address(True, False), "$")
    lcol = lcol(0)
    
End Function