How do I show number of records that meet criteria filter

[Guest]

I have a large spreadsheet containing several hundred rows of data. I want
to filter the list and I want to be able to show the number of records that
match the filter criteria. For example, un-filtered the total number of
records in the list is displayed and, when filtered, the number of records in
the list that match the criteria is displayed.

Can onyone help me please?

Kind regards,

Cliff

 

[Bob Phillips]

Cliff,

Here is a little function that returns the count, and works from VBA. Just
pass the header cell to the function

Function FilteredListCount(rng As Range)
Dim iLastrow As Long
Dim rngTemp As Range
iLastrow = Cells(Rows.Count, rng.Column).End(xlUp).Row
Set rngTemp = rng.Offset(1, 0).Resize(iLastrow - 1)
FilteredListCount = rngTemp.SpecialCells(xlCellTypeVisible).Count
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

If you can pick out a column that always has something in it, you can use:

=subtotal(3,a2:a99)

 

[Jim May]

There is also:
subtotal(3,A2:A100) << 3 -For the count