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

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

Any help will be greatly appreciated.

Thanks in advance!

Matt

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

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

Hi
ONE Solution
Paste the following sub into a code Module.
It creates an array constant called "ShownRows" which identifies the
visible rows in Range("SomeData"). You will need to change this range
to the one you require or Select the data before filtering and change
the Range to Selection.

Public Sub Create_Filtered_Array()
'Creates an array of true/false for a row not hidden/hidden
'array is named to be used by worksheet
'This array is used in worksheet functions to apply them to filtered
data
only

Dim rgRow As Range
Dim FilterArray() As Boolean
Dim rownumber As Integer
Dim k As Integer

With Range("SomeData") 'The data you are filtering
rownumber = .Rows.Count
ReDim FilterArray(1 To rownumber, 1 To 1)
For k = 1 To rownumber
FilterArray(k, 1) = Not .Rows(k).EntireRow.Hidden
'Creates a column array of Boolean with TRUE for visible, so that it
can be
compared with a worksheet Range column
Next k
End With
Names.Add Name:="ShownRows", RefersTo:=FilterArray
'Creates a named array constant, consisiting of a column of Booleans
End Sub

You can run this sub from a macro button on the worksheet.

Now the summing bit.
Without filter on, you might add up the number of phones in all rooms
using, say,
=sum(C1:C100)
where I am assuming the number of phones in each room is in C1:C100.
Replace this formula with
{=sum(if(ShownRows, C1:C100))}

where the {} brackets mean enter as an array formula using
Ctrl+Shift+Enter.

So your sequence of actions is:
Put on Autofilter
run Create_Filtered_Array() from the macro button

Now see all your sum totals adjust for the filtered data.

regards
Paul

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

Thank you for the feedback!

It looks like I've got something to get me started!


Matt

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