Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row function....with a twist
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row function....with a twist
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row function....with a twist
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row function....with a twist
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row function....with a twist
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row function....with a twist
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row function....with a twist
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum with a Twist Q | Excel Worksheet Functions | |||
If but with a twist:( | New Users to Excel | |||
IF function - with a twist! | Excel Discussion (Misc queries) | |||
Sum with a twist | Excel Discussion (Misc queries) | |||
Function To Add across Multiple Sheets with a Twist | Excel Worksheet Functions |