iwelcomesu
05-27-2011, 07:06 AM
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
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