Finding last row of every sheet
 

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

Finding last row of every sheet
 

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

Finding last row of every sheet
 

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

Finding last row of every sheet
 

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

Finding last row of every sheet
 

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

Finding last row of every sheet
 

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

Finding last row of every sheet
 

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.

--
Regards,
Tom Ogilvy


"Ron de Bruin" wrote in message
...
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

Finding last row of every sheet
 

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.
--
Gary's Student


"Ron de Bruin" wrote:

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

Finding last row of every sheet
 

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

Finding last row of every sheet
 

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

Finding last row of every sheet
 

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