Help to improve UDF please

[michael.beckinsale]

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

 

[Ron de Bruin]

Hi michael

I will look at it this evening

See also
http://www.rondebruin.nl/last.htm

 

[Ron de Bruin]

Hi michael

Sorry, no time today.
If I have time I will look at it this week

good night

 

[Dave D-C]

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

 

[Peter T]

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

 

[Dave D-C]

I've got to eat crow again:

Set zRng = Sheets(MySheet).Rows(MyRow)
LastCol = zRng.SpecialCells(xlCellTypeLastCell).Column

does not give the last used column in MyRow.

 

[michael.beckinsale]

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

 

[Peter T]

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]

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

 

[michael.beckinsale]

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

 

[Peter T]

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]

Hi Peter,

Thanks for the advice.