Filter Macro

[alexduy]

Hi,

If column "A" has several different type of class IDs and my macro filtered by one class ID and column "B" is left with 4 different departments after the filtered. How do I write a macro to select 3 departments out of the 4 departments in column "B"? I want a macro to be dynamic, so that when a new department is added it will filter 4 departments, and leave just one department. It will always uncheck department 4. Make senses?

My current code is as follow (this code works, but when a new department added, it will not capture the new department):

Please Login or Register  to view this content.

Thank you for your help

[dflak]

First of all it looks like you might be missing a closing parenthesis.

Here's how I would do it. Make a pivot table on your data with totals. The only thing in this pivot table should be the department name in the row area. This gives you a unique list of departments. I did this on a tab called pivots. I took the results and made a named dynamic range out of them called Departments. =OFFSET(Pivot!$A$2,0,0,COUNTA(Pivot!$A:$A)-1,1).

Then I used the following VB code to generate a list of departments other than department 4.

Please Login or Register  to view this content.

You should be able to plug this function into your code.

Please Login or Register  to view this content.