Link to home
Start Free TrialLog in
Avatar of Tom Farrar
Tom FarrarFlag for United States of America

asked on

Row Count for All worksheets in workbook

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
ASKER CERTIFIED SOLUTION
Avatar of Tracy
Tracy
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No points for this, please!

Change:

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

to:

        totalRows = totalRows + Application.WorksheetFunction.CountA(wkSht.Range("A:A"))
Avatar of Tom Farrar

ASKER

Thanks to both of you.....