How do I show number of records that meet criteria filter

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

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)


"CliffD" <[email protected]> wrote in message
news:[email protected]...
> 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

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

=subtotal(3,a2:a99)



CliffD wrote:
>
> 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

--

Dave Peterson

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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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)
>
>
> "CliffD" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>