Counting Visible Cells

[Paul Riley]

Hi,

In a large data set with auto filters I can use the function =SUBTOTAL(9,Range) to sum data only in the visble rows.

Is there a similar function to COUNT the number of visible rows when the filters are switched on & off?

Thanks,

Paul

 

[Boller]

Yes, have a look at SUBTOTAL in the Help file.

 

[DominicB]

Good afternoon Paul Riley

Use the subtotal parameter of 2 :

=SUBTOTAL(2,A1:A100)

HTH

DominicB

 

[Paul Riley]

HIDDENROWS

Hi,

Thanks for identifying the SUBTOTAL(2 or 102, Range) function. This works fine for counting if the range has numbers - how can I get it to work if the range has text? i.e count the number of visible rows in a text only data base?

thanks,

Paul

 

[DominicB]

Hi Paul Riley

In that case, I'm not sure there is one, but would be quite happy to be proved wrong...

You could try using this custom function :

Function CountMyRange(UsrRng As Range)
Dim cell As Range
Application.Volatile
CountMyRange = 0
For Each cell In UsrRng
If Not cell.EntireRow.Hidden = True Then
CountMyRange = CountMyRange + 1
End If
Next cell
End Function

Paste the code into an ordinary blank module, and then use this formula to count your filtered rows :

=CountMyRange(A1:A100)

HTH

DominicB