A macro to Count the number of rows across all worksheets
Posted on 2016-09-23
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.
WorkBook with worksheets: Instructions, Table of contents, Abc1, Abc2, Abc3, Abc4, ............
Summary Worksheet would display:
The below code has some limitations like excluding the desired worksheet and the rows after row number 7 needs to be taken into consideration.
For Each Sheet In ThisWorkbook.Sheets
Debug.Print Sheet.Name & vbTab & CountMyRows(Sheet.Name)
Function CountMyRows(SName) As Long '# where SName is the name of a sheet
Dim rowCount As Long
rowCount = Worksheets(SName).UsedRange.Rows.Count
CountMyRows = rowCount
Thanks in advance