Count Total Lines across all worksheets

[bradjedis]

Greetings,

Wondering if there is a way to cycle thru all worksheets in a workbook, and get a total of ALL the rows that contain data. note: col A always contains data.

[HansV]

Run this macro:

Code: Select all

Sub CountAllRows()
    Dim w As Worksheet
    Dim i As Long
    Dim m As Long
    Dim n As Long
    For Each w In Worksheets
        i = i + 1
        m = w.Range("A" & w.Rows.Count).End(xlUp).Row
        n = n + m
    Next w
    MsgBox "This workbook contains " & n & " row(s) in " & i & " worksheet(s)", vbInformation
End Sub

[snb]

Or:

Code: Select all

Sub M_snb()
   On Error Resume Next
   For Each it In Sheets
     y = y + it.Columns(1).SpecialCells(2).Count
     y = y + it.Columns(1).SpecialCells(-4123).Count
   Next
   
   MsgBox y
End Sub