Best start again, there's too much wrong to correct !

Function LRo(Optional MyWB As Variant, Optional MySh As Variant, Optional
MyCol As String) As Long
Dim arg1Type As VbVarType, arg2Type As VbVarType
Dim rCell As Range
Dim ws As Worksheet
Dim wb As Workbook

If Not IsMissing(MyWB) Then arg1Type = VarType(MyWB)
If Not IsMissing(MySh) Then arg2Type = VarType(MySh)

On Error GoTo errH
If arg2Type = vbObject Then
'it's an object, assume it's a Worksheet
Set ws = MySh
Else
If arg1Type = vbObject Then
' assume it's a Workbook
Set wb = MyWB
ElseIf arg1Type = vbString Then
Set wb = Application.Workbooks(MyWB)
Else
Set wb = ActiveWorkbook
End If
If arg2Type = vbString Then
Set ws = wb.Worksheets(MySh)
ElseIf wb Is ActiveWorkbook Then
Set ws = ActiveSheet
Else
' it's not the activeworkbook but we don't know what sheet
Err.Raise 12345, , "Can't determine what sheet"
End If
End If

With ws
If MyCol = "" Then
Set rCell = .Cells.Find( _
What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
If Not rCell Is Nothing Then
LRo = rCell.Row
Else ' empty sheet
LRo = 0 ' or maybe LRo = 1 depending on needs
End If
Else
Set rCell = .Cells(.Rows.Count, CLng(MyCol)).End(xlUp)
If rCell.Row = 1 And Len(r) = 0 Then
LRo = 0 ' column is empty, but maybe LRo = 1 depending on
needs
Else
LRo = rCell.Row
End If

End If
End With
Exit Function
errH:
LRo = -1 ' flag an error

End Function


I can't imagine using this myself, anyway you can supply Workbook and/or
Worksheets as string or objects, or nothing at all.

Regards,
Peter T



"michael.beckinsale" wrote in message
...
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