Put the function in your standard module 1. Then use it as shown
in the sample Sub beneath the function.

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Sub xyz()
x = LastRow(Sheets(1))
MsgBox x
End Sub


"Jim Thomlinson" wrote:

Here is the function that I use to get the bottom right most cell on a given
sheet.

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function

You can use it like this...
My_LastRow = lastcell(sheet4).row
--
HTH...

Jim Thomlinson


"Keith R" wrote:


I have code used to append new records (rows) on sheet 4. In the past, I use
the lastrow procedure (below, from this newsgroup) to find the last row
where I can add new data. however, in this particular case, it keeps trying
to add my data near the top of the worksheet. So I used the rows.count to
start troubleshooting, and got the correct value there even though the
lastrow procedure is still at the top of the sheet. I have saved, closed,
and re-opened the workbook, to no avail.

Any ideas why these two would return different values?

Thanks,
Keith
XP/XL2003

(misc code deleted)

My_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
MsgBox Sheet4.UsedRange.Rows.Count
'returns a value of 77, which is accurate
MsgBox My_LastRow
'returns a value of 2, then after adding a record it returns a value
of 3.