You didn't fail miserably. You only have one problem.

For missing parameter your don't use "IS Nothing", instead use ISMissing as
one word.

"michael.beckinsale" wrote:

Hi All,

Below is a 'Last Row' function that l have been using which works
fine, as long as a string argument is entered. What l would like to do
is either :

1) amend the function so that it also accepts variables (preferred
solution)
2) write a new function along the same lines that accepts variables

To illustrate what l mean by the above say you have the following
statement in a procedure

Dim MyWB As Workbook
Dim MySH As Worksheet
Set MyWB = Workbooks("Book1")
Set MySH = Sheets("Sheet1")

I would like to then pass the variables MyWB and MySH to the function
to return the last row.

This is the code l have used to try and achieve this but failed
miserably!
Function LRo(Optional MyWb As Variant, Optional MySh As Variant,
Optional MyCol As String) As Long

If MyWb Is Nothing Then MyWb = ThisWorkbook
If MySh Is Nothing Then MySh = ActiveSheet

With MyWb.MySh
If MyCol = "" Then
LRo = .Cells.Find( _
What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Else
LRo = .Cells(Rows.Count, MyCol).End(xlUp).Row
End If
End With

End Function

This is the code l currently use for getting the last row but using
string values
Function LRs(Optional MyWb As String, Optional MySh As String,
Optional MyCol As String) As Long

If MyWb = "" Then MyWb = ThisWorkbook.Name
If MySh = "" Then MySh = ActiveSheet.Name
With Workbooks(MyWb).Sheets(MySh)
If MyCol = "" Then
LRs = .Cells.Find( _
What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Else
LRs = .Cells(Rows.Count, MyCol).End(xlUp).Row
End If
End With

End Function

All help with this problem / any further ideas greatly appreciated.

Regards

Michael