Thanks
The data is very sensitive so I can't send the workbook

"Ron de Bruin" wrote:

This function is working correct
Function lastrow(sh As Worksheet)

Maybe there is a space in a cell below your real data

Send me your problem workbook private and I look at it


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Gwen" wrote in message ...
No, not at all. The last row range from 13 to 2554.
I am using the harlan function you provided. Thank you.
However, I would appreciate some guidance in using the function to place
the last row value in "A1" of ever sheet.

thx

"Ron de Bruin" wrote:

Wrong results ?

Is the row number to high ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Gwen" wrote in message ...
I get the number for the last for only some of sheets in cell N1.
Not all of the sheets.

"Ron de Bruin" wrote:

Hi Gwen

What is not working ?

You can also try Harlan's Function that you can use in a worksheet cell

Function lr(Optional r As Range) As Variant
Dim ur As Range, c As Range, i As Long, n As Long

'there arguments for & against volatility - optional to uncomment
Application.Volatile

If r Is Nothing Then Set r = Application.Caller
If Not TypeOf r Is Range Then Set r = ActiveCell

Set ur = r.Parent.UsedRange
n = ur.Rows.Count

For i = n To 1 Step -1
Set c = ur.Cells(i, 1)
If Not IsEmpty(c.Value) Then Exit For
If Not IsEmpty(c.End(xlToRight).Value) Then Exit For
Next i

lr = ur.Row + i - 1
End Function

Then use this in a worksheet cell

=lr()




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Gwen" wrote in message ...
Hi,
Please help. I am trying to get the last row of every shee to use in a
sumproduct formula.
I am using the below code. For some reason it only works on some of the
sheets.

Thx


Sub shname()
Dim wks As Worksheet
Dim shlast As Long

Dim r As Range


For Each wks In ThisWorkbook.Worksheets


shlast = lastrow(wks)
Set r = wks.Range("N1")


r.Value = shlast


Next wks
Application.ScreenUpdating = True


End Sub

Function lastrow(sh As Worksheet) ('found on the web)
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