Milind Agarwal
asked on
A macro to Count the number of rows across all worksheets
Hi All,
Need some help in coming up with a VBA. A summary worksheet which would print all the worksheet names and counts of all the used rows(After row 7) of the worksheets. I also want to exclude the worksheets like table of contents.
example:
WorkBook with worksheets: Instructions, Table of contents, Abc1, Abc2, Abc3, Abc4, ............
Summary Worksheet would display:
Worksheet Counts
Abc1 20
Abc2 100
Abc3 217
Abc4 530
.
.
.
Total 867
The below code has some limitations like excluding the desired worksheet and the rows after row number 7 needs to be taken into consideration.
Function Test_It()
For Each Sheet In ThisWorkbook.Sheets
Debug.Print Sheet.Name & vbTab & CountMyRows(Sheet.Name)
Next Sheet
End Function
Function CountMyRows(SName) As Long '# where SName is the name of a sheet
Dim rowCount As Long
rowCount = Worksheets(SName).UsedRang e.Rows.Cou nt
CountMyRows = rowCount
End Function
Thanks in advance
Need some help in coming up with a VBA. A summary worksheet which would print all the worksheet names and counts of all the used rows(After row 7) of the worksheets. I also want to exclude the worksheets like table of contents.
example:
WorkBook with worksheets: Instructions, Table of contents, Abc1, Abc2, Abc3, Abc4, ............
Summary Worksheet would display:
Worksheet Counts
Abc1 20
Abc2 100
Abc3 217
Abc4 530
.
.
.
Total 867
The below code has some limitations like excluding the desired worksheet and the rows after row number 7 needs to be taken into consideration.
Function Test_It()
For Each Sheet In ThisWorkbook.Sheets
Debug.Print Sheet.Name & vbTab & CountMyRows(Sheet.Name)
Next Sheet
End Function
Function CountMyRows(SName) As Long '# where SName is the name of a sheet
Dim rowCount As Long
rowCount = Worksheets(SName).UsedRang
CountMyRows = rowCount
End Function
Thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome Milind! Glad to help.
ASKER