I think the closest you can do is to rely on the worksheet_calculate and a
formula in a cell in that worksheet:

Option Explicit
Private Sub Worksheet_Calculate()
MsgBox Me.AutoFilter.Range.Columns(1) _
.Cells.SpecialCells(xlCellTypeVisible).Cells.Count - 1
End Sub

But xl2002 didn't recalc when I just changed the filter.

So I threw:
=subtotal(3,a:a)
in an out of the way spot.

to force a recalc when I changed the filter

But if you're going to add the =subtotal() function, maybe you could just refer
to it directly in your code.




"BVHis <" wrote:

Being the newcomer to Excel VBA, I was wondering if there's a way of
knowing when the Autofilter has been changed, and if so, how??

What I'm trying to accomplish is this: I have a spreadsheet that is
essentially a BOM of extracted data from AutoCAD. This data consists
of a room numbers, room names, # of PCs, # of phones, etc... If the
user filters the list by, let's say the Room Number, I'd like to be
able to then count how many of each item are in that room. I know how
to get the visible rows but I don't know how to capture when the filter
has been changed.

Any help will be greatly appreciated.

Thanks in advance!

Matt W

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson