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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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