Loop Thru Worksheets / MsgBox Rows disconnect

[MikeL]

Hi,

There are 3 worksheets named A, B and C. I want to loop thru each WS and MsgBox Count of Rows and WS Name. Weird that I keep getting same count of rows from Worksheet A in MsgBox even though the other MsgBox pops up with the 3 different sheet names.
What needs to change in code?


--------------------------------
Sub ABC()

"Declare
Dim Current As Worksheet
Dim lrow As Long


' Loop thru Worksheets A B and C and MsgBox number of rows and then MsgBox Name of Worksheet

For Each Current In Worksheets

lrow = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox (Str(lrow))


lrow = 0
MsgBox Current.Name

Next

MsgBox ("complete")


End Sub

 

[Michael M]

Try this

lSub ABC()

'Declare
Dim Current As Worksheet
Dim lrow As Long


' Loop thru Worksheets A B and C and MsgBox number of rows and then MsgBox Name of Worksheet

For Each Current In Worksheets

lrow = Current.Cells(Rows.Count, "A").End(xlUp).Row
MsgBox Current.Name & " Has " & lrow & " rows"

Next

MsgBox ("complete")


End Sub

 

[My Aswer Is This]

Try this:

Sub Sheet_Names_And_Rows()
'Modified  2/17/2019  7:48:06 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
For i = 1 To Sheets.Count
    Lastrow = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
    MsgBox "Sheet Named" & vbNewLine & Sheets(i).Name & vbNewLine & "Has" & vbNewLine & Lastrow & vbNewLine & "Rows"
Next
Application.ScreenUpdating = True
End Sub

 

[MikeL]

That worked. Thanks so much!

Can you tell why Current needed to be in front of Cells ? It seems if it was not , the last row was only on Worksheet A.