Row count with hidden rows (filters on)

[Sbrockey]

I have a table A3 : L300. Row 3 has column headers and filters. I want to count non-hidden rows once filters are set. For example after filter are set rows 12, 124, 128, 246 and 298 are not hidden so result would be 5 or 6 including header row.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Take a look at the AGGREGATE command if you have a recent version of Excel (2010 or later).

A summary of the options is available here and probably lots of other places on the web: MS Excel: AGGREGATE Function (WS)

The following command runs the equivalent of a COUNT() function on A2:A71 but ignores hidden rows:

=AGGREGATE(2,5,A2:A71)

 

[Sbrockey]

Can you use Aggregate in VBA?

 

[Jonmo1]

Can you use Aggregate in VBA?

It's actually even easier in VBA

MsgBox Range("A2:A71").SpecialCells(xlCellTypeVisible).Count

 

[Sbrockey]

It's actually even easier in VBA

MsgBox Range("A2:A71").SpecialCells(xlCellTypeVisible).Count

Thanks worked great.

 

[Jonmo1]

You're welcome.