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 |
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 |