Excellent function! Much more efficient than the method I am currently using.
I will be incorporating this into many of my VBA projects. Can this be
modified to return the last row of each column (or a specified range) in a
sheet with columns of different lengths?

Currently I use the following function:

Function LastRow(intReferenceColumn As Integer, intStartRow As Integer)
Dim intLR as integer
intLR = intStartRow - 1
Do
intLR = intLR + 1
Loop Until Application.ActiveSheet.Cells(intLR, intReferenceColumn) = ""
intLR = intLR - 1
LastRow = intLR
End Function

This allows me to specify a column and a row to start counting rows from. Of
course there are obvious drawbacks to this method:
1.)If there are any empty rows before the end of the data in that column,
the function will stop there.

2.) looping through the rows to find the end introduces inneficiencies into
my code that I really could stand to live without. It is ok if the function
is only called once or twice in any given project, but when the function is
being called multiple times within the same line of code, it can become an
issue.

Thanks,
Steve


"Patrick Molloy" wrote:

Function GetLastRow() As Long
With ActiveSheet.UsedRange
GetLastRow = .Row + .Rows.Count - 1
End With
End Function

You could add a worksheet object as parameter...

Function GetLastRow(ws as Worksheet) As Long
With ws.UsedRange
GetLastRow = .Row + .Rows.Count - 1
End With
End Function

"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