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