We help IT Professionals succeed at work.

Row Count for All worksheets in workbook

Tom Farrar
Tom Farrar asked
on
I have the following code that attempts to count all the column A rows with data in all the worksheets, but it doesn't do that.  It looks like it counts the rows in the active worksheet as many times as there are worksheets in the workbook.  For instance, if the active worksheet has 5 rows with data in it, and there are 6 worksheets in the book, the count given by the code is 30. Can you help me identify the problem with the code?  Thanks.

Sub totalRows()

    Dim gtRows As Long
   
    For Each wksht In ThisWorkbook.Sheets
        gtRows = gtRows + Application.WorksheetFunction.CountA(Range("A:A"))
    Next

    MsgBox "The total number of rows used in this workbook is: " & gtRows

End Sub
Comment
Watch Question

VBA Developer
Commented:
Try this:

Sub countRows()
    
    Dim wkSht As Worksheet
    Dim totalRows As Long
    
    For Each wkSht In ThisWorkbook.Worksheets
        totalRows = totalRows + Application.WorksheetFunction.CountA(Range("A:A"))
    Next
    
    MsgBox "The total number of used rows in Column A is: " & totalRows

End Sub

Open in new window

Book1.xls
Top Expert 2010

Commented:
No points for this, please!

Change:

        totalRows = totalRows + Application.WorksheetFunction.CountA(Range("A:A"))

to:

        totalRows = totalRows + Application.WorksheetFunction.CountA(wkSht.Range("A:A"))

Author

Commented:
Thanks to both of you.....