Finding last row of every sheet

[Gwen]

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

 

[Ron de Bruin]

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()

 

[Gary''s Student]

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

 

[Gwen]

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

 

[Ron de Bruin]

Hi Gary's Student

UsedRange is not always the correct range

See
http://www.contextures.com/xlfaqApp.html#Unused

 

[Ron de Bruin]

Wrong results ?

Is the row number to high ?

 

[Tom Ogilvy]

Usedrange is always the correct range if you want what excel is maintaining
detailed information on.

If you want the last cell containing data, then that isn't what it provides.

 

[Gary''s Student]

Thank you Ron. Is UsedRange always available from other than ActiveSheet??

Perhaps a simpler solution is anticipate the need and to define a Named
Range on each sheet that corresponds to the last row on that sheet.

Each last row would then always be available.

 

[Gwen]

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]

This function is working correct
Maybe there is a space in a cell below your real data

Send me your problem workbook private and I look at it

 

[Gwen]

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