How to get the count of visible rows after filtering

priyanka.R

New Member
Joined
Mar 7, 2011
Messages
9
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

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

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

xlstheronstyle

New Member
Joined
Mar 8, 2011
Messages
3
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
 

Forum statistics

Threads
1,141,734
Messages
5,708,166
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top