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

--
jeff

... I used to have a handle on life, but it broke.

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..

---
Regards,
Norman



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

--
jeff

.. I used to have a handle on life, but it broke.

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,

--
jeffP


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

---
Regards,
Norman



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

--
jeff

.. I used to have a handle on life, but it broke.