Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
Hi All,
The code below is a UDF to find the last row or last column on a worksheet with optional parameters for specific columns, rows & worksheets. It works 'As is" but l cant help feeling that it can be improved. Specifically a) l would like the user to be able to enter the 1st parameter (RorC) as the letters R or C without the quotation marks b) I would like to be able to prompt the user to enter the 'MySh' parameter as a string ie "Sheet1" which is required rather than them thinking they need to enter Sheets("Sheet1") c) Put some error checking in re parameter combinations (ie R can only be combined with 'MyCol' and C can only be combined with 'MyRow' All other comments & suggestions for improving this UDF will be greatly appreciated. Public Function LastRC(RorC As String, Optional MySh, Optional MyRow As Long, Optional MyCol As String) Dim Choice As Integer If RorC = "R" Or RorC = "r" Then Choice = 1 If RorC = "C" Or RorC = "c" Then Choice = 2 If IsMissing(MySh) Then MySh = ActiveSheet.Name Select Case Choice Case 1 On Error Resume Next If MyCol = "" Then LastRC = Sheets(MySh).Cells.Find(What:="*", _ After:=Cells(1, 1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).row Else LastRC = Sheets(MySh).Cells(Rows.Count, MyCol).End(xlUp).row End If On Error GoTo 0 Case 2 On Error Resume Next If MyRow = 0 Then LastRC = Sheets(MySh).Cells.Find(What:="*", _ After:=Cells(1, 1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Else LastRC = Sheets(MySh).Cells(MyRow, Columns.Count).End(xlToLeft).Column End If On Error GoTo 0 End Select End Function Use Like : MyLastColumn = LastRC("C", "Sheet2", 6) Regards Michael Beckinsale |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
Hi michael
I will look at it this evening See also http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "michael.beckinsale" wrote in message ups.com... Hi All, The code below is a UDF to find the last row or last column on a worksheet with optional parameters for specific columns, rows & worksheets. It works 'As is" but l cant help feeling that it can be improved. Specifically a) l would like the user to be able to enter the 1st parameter (RorC) as the letters R or C without the quotation marks b) I would like to be able to prompt the user to enter the 'MySh' parameter as a string ie "Sheet1" which is required rather than them thinking they need to enter Sheets("Sheet1") c) Put some error checking in re parameter combinations (ie R can only be combined with 'MyCol' and C can only be combined with 'MyRow' All other comments & suggestions for improving this UDF will be greatly appreciated. Public Function LastRC(RorC As String, Optional MySh, Optional MyRow As Long, Optional MyCol As String) Dim Choice As Integer If RorC = "R" Or RorC = "r" Then Choice = 1 If RorC = "C" Or RorC = "c" Then Choice = 2 If IsMissing(MySh) Then MySh = ActiveSheet.Name Select Case Choice Case 1 On Error Resume Next If MyCol = "" Then LastRC = Sheets(MySh).Cells.Find(What:="*", _ After:=Cells(1, 1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).row Else LastRC = Sheets(MySh).Cells(Rows.Count, MyCol).End(xlUp).row End If On Error GoTo 0 Case 2 On Error Resume Next If MyRow = 0 Then LastRC = Sheets(MySh).Cells.Find(What:="*", _ After:=Cells(1, 1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Else LastRC = Sheets(MySh).Cells(MyRow, Columns.Count).End(xlToLeft).Column End If On Error GoTo 0 End Select End Function Use Like : MyLastColumn = LastRC("C", "Sheet2", 6) Regards Michael Beckinsale |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
Hi michael
Sorry, no time today. If I have time I will look at it this week good night -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi michael I will look at it this evening See also http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "michael.beckinsale" wrote in message ups.com... Hi All, The code below is a UDF to find the last row or last column on a worksheet with optional parameters for specific columns, rows & worksheets. It works 'As is" but l cant help feeling that it can be improved. Specifically a) l would like the user to be able to enter the 1st parameter (RorC) as the letters R or C without the quotation marks b) I would like to be able to prompt the user to enter the 'MySh' parameter as a string ie "Sheet1" which is required rather than them thinking they need to enter Sheets("Sheet1") c) Put some error checking in re parameter combinations (ie R can only be combined with 'MyCol' and C can only be combined with 'MyRow' All other comments & suggestions for improving this UDF will be greatly appreciated. Public Function LastRC(RorC As String, Optional MySh, Optional MyRow As Long, Optional MyCol As String) Dim Choice As Integer If RorC = "R" Or RorC = "r" Then Choice = 1 If RorC = "C" Or RorC = "c" Then Choice = 2 If IsMissing(MySh) Then MySh = ActiveSheet.Name Select Case Choice Case 1 On Error Resume Next If MyCol = "" Then LastRC = Sheets(MySh).Cells.Find(What:="*", _ After:=Cells(1, 1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).row Else LastRC = Sheets(MySh).Cells(Rows.Count, MyCol).End(xlUp).row End If On Error GoTo 0 Case 2 On Error Resume Next If MyRow = 0 Then LastRC = Sheets(MySh).Cells.Find(What:="*", _ After:=Cells(1, 1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Else LastRC = Sheets(MySh).Cells(MyRow, Columns.Count).End(xlToLeft).Column End If On Error GoTo 0 End Select End Function Use Like : MyLastColumn = LastRC("C", "Sheet2", 6) Regards Michael Beckinsale |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
michael,
This is my "other comments & suggestions". I don't know how you want to input the paramters or express errors. So no error checking. Public Function LastRC(RorC$, Optional MySh$, Optional MyRow&, Optional MyCol$) Dim zRng As Range If IsMissing(MySh) Then MySh = ActiveSheet.Name Set zRng = Sheets(MySh).Cells Select Case RorC Case "R", "r" If MyCol < "" Then Set zRng = Sheets(MySh).Columns(MyCol) LastRC = zRng.SpecialCells(xlCellTypeLastCell).Row Case "C", "c" If MyRow < 0 Then Set zRng = Sheets(MySh).Rows(MyRow) LastRC = zRng.SpecialCells(xlCellTypeLastCell).Column End Select End Function "michael.beckinsale" wrote in message ups.com... Hi All, The code below is a UDF to find the last row or last column on a worksheet with optional parameters for specific columns, rows & worksheets. It works 'As is" but l cant help feeling that it can be improved. Specifically a) l would like the user to be able to enter the 1st parameter (RorC) as the letters R or C without the quotation marks b) I would like to be able to prompt the user to enter the 'MySh' parameter as a string ie "Sheet1" which is required rather than them thinking they need to enter Sheets("Sheet1") c) Put some error checking in re parameter combinations (ie R can only be combined with 'MyCol' and C can only be combined with 'MyRow' All other comments & suggestions for improving this UDF will be greatly appreciated. Public Function LastRC(RorC As String, Optional MySh, Optional MyRow As Long, Optional MyCol As String) Dim Choice As Integer If RorC = "R" Or RorC = "r" Then Choice = 1 If RorC = "C" Or RorC = "c" Then Choice = 2 If IsMissing(MySh) Then MySh = ActiveSheet.Name Select Case Choice Case 1 On Error Resume Next If MyCol = "" Then LastRC = Sheets(MySh).Cells.Find(What:="*", _ After:=Cells(1, 1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).row Else LastRC = Sheets(MySh).Cells(Rows.Count, MyCol).End(xlUp).row End If On Error GoTo 0 Case 2 On Error Resume Next If MyRow = 0 Then LastRC = Sheets(MySh).Cells.Find(What:="*", _ After:=Cells(1, 1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Else LastRC = Sheets(MySh).Cells(MyRow, Columns.Count).End(xlToLeft).Column End If On Error GoTo 0 End Select End Function Use Like : MyLastColumn = LastRC("C", "Sheet2", 6) Regards Michael Beckinsale ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
Pending Ron's return, some comments following only a quick glance of your
UDF a) l would like the user to be able to enter the 1st parameter (RorC) as the letters R or C without the quotation marks Would need to define names in the workbook that uses the UDF (not the wb containing the UDF if different). The value of the name is evaluated in the UDF. Only problem is you can't use names 'r' or 'c', but could do, say Name: row refersto: ="row" or ="r" or =1 b) I would like to be able to prompt the user to enter the 'MySh' parameter as a string ie "Sheet1" which is required rather than them thinking they need to enter Sheets("Sheet1") Why would user think of entering "Sheets("Sheet1")" iso "Sheet2", I don't follow the problem. In the UDF - Dim ws as Worksheet change - If IsMissing(MySh) Then MySh = ActiveSheet.Name to - If IsMissing(MySh) then set ws = application.caller.parent else set ws = application.caller.parent.parent.worksheets(MySht) end if change LastRC = Sheets(MySh).Cells.Find(What:="*", _ After:=Cells(1, 1), _ Lookat:=xlPart, _ etc to With ws LastRC = .Cells.Find(What:="*", _ After:=.Cells(1, 1), _ etc End with note the dot to qualify .cells(1,1) or .Range("A1") to the worksheet c) Put some error checking in re parameter combinations (ie R can only be combined with 'MyCol' and C can only be combined with 'MyRow' Why not replace 'MyCol' & 'MyRow' with one arg 'MyRowOrCol' Why are your arg's declared like this - Optional MyRow As Long, Optional MyCol As String) surely both longs, or maybe variants in case user thinks need to add quotes As for error handling, On error goto errH 'code Exit function LastRC = CVErr(xlErrValue) end function could check the err.number and return more feedback cause of error Bear in mind on its own the UDF will not recalc with changing last 'data' cell Regards, Peter T "michael.beckinsale" wrote in message ups.com... Hi All, The code below is a UDF to find the last row or last column on a worksheet with optional parameters for specific columns, rows & worksheets. It works 'As is" but l cant help feeling that it can be improved. Specifically a) l would like the user to be able to enter the 1st parameter (RorC) as the letters R or C without the quotation marks b) I would like to be able to prompt the user to enter the 'MySh' parameter as a string ie "Sheet1" which is required rather than them thinking they need to enter Sheets("Sheet1") c) Put some error checking in re parameter combinations (ie R can only be combined with 'MyCol' and C can only be combined with 'MyRow' All other comments & suggestions for improving this UDF will be greatly appreciated. Public Function LastRC(RorC As String, Optional MySh, Optional MyRow As Long, Optional MyCol As String) Dim Choice As Integer If RorC = "R" Or RorC = "r" Then Choice = 1 If RorC = "C" Or RorC = "c" Then Choice = 2 If IsMissing(MySh) Then MySh = ActiveSheet.Name Select Case Choice Case 1 On Error Resume Next If MyCol = "" Then LastRC = Sheets(MySh).Cells.Find(What:="*", _ After:=Cells(1, 1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).row Else LastRC = Sheets(MySh).Cells(Rows.Count, MyCol).End(xlUp).row End If On Error GoTo 0 Case 2 On Error Resume Next If MyRow = 0 Then LastRC = Sheets(MySh).Cells.Find(What:="*", _ After:=Cells(1, 1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Else LastRC = Sheets(MySh).Cells(MyRow, Columns.Count).End(xlToLeft).Column End If On Error GoTo 0 End Select End Function Use Like : MyLastColumn = LastRC("C", "Sheet2", 6) Regards Michael Beckinsale |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
I've got to eat crow again:
Dave D-C wrote: Set zRng = Sheets(MySheet).Rows(MyRow) LastCol = zRng.SpecialCells(xlCellTypeLastCell).Column does not give the last used column in MyRow. ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
Hi All,
Many thanks for all your input. I have taken on board your comments and made some amendments. My aim was to build 1 'universal' intuitive function that could be used anywhere in the coding of a project to return the last row or last column and in which if using the optional parameters could be more specific. Additionally when reviewing the project code it should be obvious what is being done. I have decided that it is much better and intuitive to split this 'universal' function into a function each for column & row. Peter - Your comment re making MyCol a variant as opposed to a string is noted and would make the function more flexible but l feel that generally referring to a column by its alpha character makes the code more 'readable'. I was thinking of adding an additional argument for a 'workbook' if for instance one needed to refer to arange in an unknown range in an external workbook. Your thoughts would be appreciated. Any comments on improvement welcomed. Here is the code: '--------------------------------------------------------------------------------------- ' Procedure : LR ' DateTime : 07/11/07 11:43 ' Author : Michael Beckinsale ' Purpose : To get the last row number. Used without the optional parameters the last ' row anywhere on the ActiveSheet is returned. Using the optional parameters ' the last row in a specific column and or sheet is returned. ' Use Like : MyVar = LR or MyVar = LR("A") or MyVar = LR("A","Sheet1") '--------------------------------------------------------------------------------------- Function LR(Optional MyCol As String, Optional MySh As String) As Long If MySh = "" Then MySh = ActiveSheet.Name With Sheets(MySh) If MyCol = "" Then LR = .Cells.Find( _ What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).row Else LR = .Cells(Rows.Count, MyCol).End(xlUp).row End If End With End Function '--------------------------------------------------------------------------------------- ' Procedure : LC ' DateTime : 07/11/07 11:43 ' Author : Michael Beckinsale ' Purpose : To get the last column number. Used without the optional parameters the last ' column anywhere on the ActiveSheet is returned. Using the optional parameters ' the last column in a specific row and or sheet is returned. ' Use Like : MyVar = LC or MyVar = LC(1) or MyVar = LC(1,"Sheet1") '--------------------------------------------------------------------------------------- Function LC(Optional MyRow As Long, Optional MySh As String) As Long If MySh = "" Then MySh = ActiveSheet.Name With Sheets(MySh) If MyRow = 0 Then LC = .Cells.Find( _ What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column Else LC = .Cells(MyRow, Columns.Count).End(xlToLeft).Column End If End With End Function Regards Michael |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
Peter - Your comment re making MyCol a variant as opposed to a string
is noted and would make the function more flexible but l feel that generally referring to a column by its alpha character makes the code more 'readable'. I wasn't thinking, somehow forgot that string "A" denotes a column 1 ! Another thing, keep in mind the UDF will fail for any XL97/2000 users, for whom Find in a UDF fails. Regards, Peter T "michael.beckinsale" wrote in message ups.com... Hi All, Many thanks for all your input. I have taken on board your comments and made some amendments. My aim was to build 1 'universal' intuitive function that could be used anywhere in the coding of a project to return the last row or last column and in which if using the optional parameters could be more specific. Additionally when reviewing the project code it should be obvious what is being done. I have decided that it is much better and intuitive to split this 'universal' function into a function each for column & row. Peter - Your comment re making MyCol a variant as opposed to a string is noted and would make the function more flexible but l feel that generally referring to a column by its alpha character makes the code more 'readable'. I was thinking of adding an additional argument for a 'workbook' if for instance one needed to refer to arange in an unknown range in an external workbook. Your thoughts would be appreciated. Any comments on improvement welcomed. Here is the code: '--------------------------------------------------------------------------- ------------ ' Procedure : LR ' DateTime : 07/11/07 11:43 ' Author : Michael Beckinsale ' Purpose : To get the last row number. Used without the optional parameters the last ' row anywhere on the ActiveSheet is returned. Using the optional parameters ' the last row in a specific column and or sheet is returned. ' Use Like : MyVar = LR or MyVar = LR("A") or MyVar = LR("A","Sheet1") '--------------------------------------------------------------------------- ------------ Function LR(Optional MyCol As String, Optional MySh As String) As Long If MySh = "" Then MySh = ActiveSheet.Name With Sheets(MySh) If MyCol = "" Then LR = .Cells.Find( _ What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).row Else LR = .Cells(Rows.Count, MyCol).End(xlUp).row End If End With End Function '--------------------------------------------------------------------------- ------------ ' Procedure : LC ' DateTime : 07/11/07 11:43 ' Author : Michael Beckinsale ' Purpose : To get the last column number. Used without the optional parameters the last ' column anywhere on the ActiveSheet is returned. Using the optional parameters ' the last column in a specific row and or sheet is returned. ' Use Like : MyVar = LC or MyVar = LC(1) or MyVar = LC(1,"Sheet1") '--------------------------------------------------------------------------- ------------ Function LC(Optional MyRow As Long, Optional MySh As String) As Long If MySh = "" Then MySh = ActiveSheet.Name With Sheets(MySh) If MyRow = 0 Then LC = .Cells.Find( _ What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column Else LC = .Cells(MyRow, Columns.Count).End(xlToLeft).Column End If End With End Function Regards Michael |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
Hi Peter,
I was not aware of the XL97 restriction. I am thinking of changing 'Find' method to: LR = .UsedRange.Rows.Count LC = .UsedRange.Columns.Count so that it will work for XL97 onwards. Do you know of any pitfalls to this approach. I suspect that the .UsedRange somehow relates to XL's last cell which l know can give somewhat unexpected results. I would like to hear your thoughts Regards Michael |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
Hi Peter,
I have done some 'Googling' and the 'count' method will return undesirable results in earlier versions of XL. It seems that it is pretty much impossible to determine the real last cell (ie that contains data) without going through the process of deleting unwanted columns & rows and saving the file. I think l will have to live with what l have got so far and resort to the tried and tested methods if l have to develop anything on XL97 2000 Regards Michael |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
so that it will work for XL97 onwards.
Find in a UDF doesn't work in XL2000 either (I did mention, perhaps not clearly); the code will compile but will never find and return a cell. Should work in XL2002 onwards. I suspect that the .UsedRange somehow relates to XL's last cell which l know can give somewhat unexpected results. Normally the bottom right cell of the used range is same as the LastCell. However the usedrange also includes formats, perhaps merely row/col ht/wd or numberformats, so the last column(s) & row(s) could contain only black cells. Thinking aloud, to get say the last column, maybe loop columns of the used range backwards until specialcells-blanks-count is less than the loop-column-cells-count. Slight concern is the very real possibility of the UR extending very considerably beyond the last actual 'data' cell. There is a way to avoid loads of unnecessary looping but a bit involved. Couple of catches to be aware of: The topleft cell of the used range is not necessarily A1, so offset all from whatever the UR's topleft cell is determined to be. Cater for an empty sheet 'effectively' devoid of a UR. As you say the UR can also give some unexpected results in all sorts of different ways and reasons. Fortunately most of these scenarios are quite rare so unless your UDF return is critical probably OK to assume the UR is 'normal' (FWIW I do in fact aim to cater for the oddities but a lot of code and not in a UDF). Regards, Peter T "michael.beckinsale" wrote in message oups.com... Hi Peter, I was not aware of the XL97 restriction. I am thinking of changing 'Find' method to: LR = .UsedRange.Rows.Count LC = .UsedRange.Columns.Count so that it will work for XL97 onwards. Do you know of any pitfalls to this approach. I suspect that the .UsedRange somehow relates to XL's last cell which l know can give somewhat unexpected results. I would like to hear your thoughts Regards Michael |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help to improve UDF please
Hi Peter,
Thanks for the advice. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to improve my code? | Excel Programming | |||
Help to improve macro | Excel Programming | |||
Improve code | Excel Programming | |||
Improve code | Excel Programming | |||
How to improve this code? | Excel Programming |