Thank you Pike! That was a huge help and I think that I might finally be understanding the difference of Functions vs Subs as well as the parameters and arguments that they pass from one to another. I've revised my code to fit my goal of using the value to create a range of all data on a worksheet, as well as the optional arguments of last cell, last row, and last column in case I'd need to reference them separately.
Code:
Public Function Lastcell(Optional lcell As String, Optional nrow As Long, Optional lcol As String)
'Function finding last cell of data on worsheet
'Find Last row with data in any column
Dim Lastrow As String
nrow = ActiveSheet.UsedRange.Rows.Count
'Finding the Far Column in active worksheet with data in any row
'Must have headers/data in Row 1
Dim Farcol As String
Dim ncol As Long
ncol = ActiveSheet.UsedRange.Columns.Count
Farcol = Split(Cells(1, ncol).Address(True, False), "$")(0)
lcol = Farcol ' populate lcol
'Combine Lastrow & Farcol
Lastcell = lcol & nrow
lcell = Lastcell
lcell = lcol & nrow
End Function
Testing with MsgBox
Code:
Sub Display_LastCell()
Dim lcell As String
Dim nrow As Long
Dim lcol As String
'Display the last cell with data using Function
MsgBox "Last Col = " & Lastcell(lcell, nrow, lcol)
End Sub
Implementing into code, currently just changes fill color to yellow:
Code:
Sub Display_LastCell()
Dim lcell As String
Dim nrow As Long
Dim lcol As String
Call Lastcell(lcell, nrow, lcol)
allcells = "$A$1:$" & lcol & "$" & nrow
Set allcells = Range(allcells)
With allcells.Interior
.Pattern = xlSolid
.PatternColorIndex = xAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Stop
End Sub