Hi Gary's Student

UsedRange is not always the correct range

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


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


"Gary''s Student" wrote in message
...
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
--
Gary's Student


"Gwen" wrote:

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