fire macro on AutoFilter change

J

jeffP

Hi all,
I have code to count the visible columns after making a selection w/ auto
filter and enter the count in a cell. What I can't seem to do is get it to
update after a selecting a different criteria w/ the autofilter. To update
the count I have to run it as a macro or attach it to a command button but
that kind of defeats my hope of keeping the user interface simple. I tried
change Worksheet_Change and Selection_Change without luck. I actually would
like some other things to fire (show a userform ) when a selection is made
so any help/direction would be great.

Of course all suggestions are always appreciated

Here's the simple code:
mycount = Range("A1").CurrentRegion.Columns(1).
SpecialCells(xlVisible).Count
Range("h1").Value = mycount
 
N

Norman Jones

Hi JeffP,

To return a dynamic filter record count, you could drop the macro and use
the built-in Subtotal worksheet function. Using the function argument of 3
(CountA), might suit your needs..
 
J

jeffP

Norman,
I used the subtotal w/ counta and it 's fine. Thanks for your help. I still
would like to be able to start the macro on the auto filter change , to show
a user form if you have any ideas.
thanks again,
 
Top