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