Rows count

[iwelcomesu]

Hi,

I am useing below code for to count the no.of rows in eash sheet and make one consolidaetd sheet with sheet name and count of rows, this code is not working properly. this code is giving worng output, can some one help to resolve this issue, for your information in all the sheets data in text mode.


Sub macro1()
Dim aryData As Variant
Dim sh As Worksheet
Dim i As Long

With ActiveWorkbook

ReDim aryData(1 To .Worksheets.Count, 1 To 2)

For Each sh In .Worksheets
With sh
i = i + 1
aryData(i, 1) = sh.Name
aryData(i, 2) = .UsedRange.Rows.Count
End With
Next sh


.Worksheets.Add after:=.Worksheets(.Worksheets.Count)
i = 0
With ActiveSheet

For i = 1 To .Parent.Worksheets.Count

.Cells(i, "A").Value2 = aryData(i, 1)
.Cells(i, "B").Value2 = aryData(i, 2)
Next i
End With
End With

End Sub



My requirements are :

1. Count the rows in cloumn B in all the sheets, some times they are blank, then it should return 0.
2. Paste in a new sheet in (cloumn A = sheet name, cloumn B = no.of rows in each sheet cloumn B contians).

Thanks for your help.

Regards,
Hari

[Kenneth Hobs]

Sub macro1()
Dim aryData As Variant
Dim sh As Worksheet
Dim i As Long
Dim d As Long

With ActiveWorkbook
ReDim aryData(1 To .Worksheets.Count, 1 To 2)

For Each sh In .Worksheets
With sh
i = i + 1
aryData(i, 1) = sh.Name
d = .Range("B" & Rows.Count).End(xlUp).Row
If d = 1 And IsEmpty(.Range("B1")) Then d = 0
aryData(i, 2) = d
End With
Next sh

.Worksheets.Add after:=.Worksheets(.Worksheets.Count)
ActiveSheet.Range("A1").Resize(i, 2).Value2 = _
WorksheetFunction.Transpose(WorksheetFunction.Transpose(aryData))
End With
End Sub

[iwelcomesu]

Hi Hobs,

Excellent !, that's why I prefer this forum for support and solution.

Many thanks for your help.

Regards,
Hari