Finding last row of every sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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()
 
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.
 
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.
 
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
 
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
 
Back
Top