Count SubTotal Rows

[Rudi]

Hi,

How can I determine the amount of rows in a subtotal range collapsed to level 2

My variable counts one row where i have 5 rows in my collapsed subtotal range

myRC = Range("A3").CurrentRegion.Offset(1, 0).Resize(Range("A3").CurrentRegion.Rows.Count - 2).SpecialCells(xlCellTypeVisible).Rows.Count

Any ideas?
TX

[HansV]

When you collapse the range, the visible cells consist of several areas. The expression .Rows.Count returns the number of rows in the first area only.
You have to sum the number of rows over all areas:

Code: Select all

    Dim rngTotal As Range
    Dim rngArea As Range
    Dim myRC As Long
    Set rngTotal = Range("A3").CurrentRegion
    Set rngTotal = rngTotal.Offset(1, 0).Resize(rngTotal.Rows.Count - 2).SpecialCells(xlCellTypeVisible)
    For Each rngArea In rngTotal.Areas
        myRC = myRC + rngArea.Rows.Count
    Next rngArea

[Rudi]

That makes sense. Thanks for that clarifying explanation.
It works well TX :)