How to get the count of visible rows after filtering

[priyanka.R]

Hi,

Really worried i was searching for this code for a long for getting the count of rows once the filter is applied.I dont need to copy paste the data to another sheet to find the count.Sample data is given below.

Open 4
close 2
open 4
close 2

Once the filter is made to capture the close datas .Count to be assigned to a variable so that i can use it for other purposes.

Thanks in Advance.

 

[Richard Schollar]

Hi

If you access the Range property of the Autofilter object then you can calculate the number of rows using the SpecialCells method:

MsgBox Sheets("Sheet1").Autofilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count  'includes the header row!

 

[priyanka.R]

Thanks a lot .Its helps me in time.

 

[xlstheronstyle]

Hi Priyanka,

Well you can also do the same without VBA using SUBTOTAL and SUMPRODUCT. See Example Below

Lets Say Your Data is

A B
1 Name Value
2 Joe 100
3 Peter 200
4 Joe 100

Now you want to have Count Total when filtered..right..

you add new formula to column next to Value "=SUBTOTAL(102,B1)" without quote, and copy in all cells below. Now these cell will reflect value 1.

Next is to choose a Cell and enter formula "=SUMPRODUCT(C2:C4,$C$2:$C$4)"

Now when you filter lets say Joe, the Cell above will reflect 2 as answer.

Message me in case you have any further queries or need excel file.

Regards
xlstheronstyle