Count Autofiltered Rows?

[Guest]

Here's a quick one:

Anyone know how I can get a cell to show the number of records displayed
when autofiltering by column?

 

[Chip]

You dont need VBA, use subtotal it only examines visible cells...i.e,
use:

=SUBTOTAL(2,A1:A9), with A1:A9 being the range of all possible
cells...if you look at help you can see the other uses for subtotal
(i.e. the 2 tells it count, but you can have subtotal do things like
sum up the visible cells)

 

[Trevor Shuttleworth]

Assuming you are filtering on column A, the values are numeric and the count
is in a different column:

=SUBTOTAL(2,A:A)

If the data you are filtering is alphabetic, then use:

=SUBTOTAL(3,A2:A65536) (assuming a header in Row 1)

Adjust the range as required.

Regards

Trevor

 

[Guest]

You could enter another column with nothing in it except the header, call it
"BLANK". Next go to Date>>Subtotal and At Each Change in: 'BLANK' Use
Function: 'COUNT' Add Subtotal To: 'your column to autofilter'. Now use you
autofilter and it should do what you need.

Mike Q.

 

[Atreides]

Awesome! I was after something like this too and it works very simply and
easily.

Thanks Chip!

Atreides