Loop Thru Worksheets / MsgBox Rows disconnect

MikeL

Active Member
Joined
Mar 17, 2002
Messages
426
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
 
Last edited:

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,279
Office Version
  1. 2013
Platform
  1. Windows
Try this

Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,874
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Code:
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

Active Member
Joined
Mar 17, 2002
Messages
426
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,916
Members
409,847
Latest member
Foster034
Top