Last row function....with a twist

[michael.beckinsale]

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

 

[Joel]

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.

 

[Peter T]

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

 

[Patrick Molloy]

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

 

[Steve Danhof]

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]

Function LastColumnRow(col As String) As Long
Dim cell As Range
Set cell = Columns(col).SpecialCells(xlCellTypeLastCell)
LastColumnRow = cell.Row
End Function

 

[michael.beckinsale]

Joel / Peter T / Steve,

Many thanks for your generous input.

Sorry for the delay in replying, been a bit busy !

If l have got this right then my attempt to amend the function to a
'stand alone' (ie not incorporate into the string function) function
into which l can pass the MyWB & MySh variable only needed to have Is
Nothing replacied with Is Missing.

Is this correct?

If so it more efficient to have both the 'varaible' & 'string'
versions available within a workbook and use whichever is appropriate
or use Peter's combined version?

Peter - I didn't realise it was so complex to combine the 2 but it
will be very useful.

Steve - In answer to your question the Function(LRs.........etc) that
l posted will return the last row of whichever column you enter ie "A"
"Z" etc but if you leave that parameter blank it will return the last
row on the worksheet whatever column it is in. I have not tried it on
range!

Regards

Michael