Pass Cell Contents To Array And Filter

[jaslake]

Hi

I recently wrote a procedure that filtered a worksheet with this line of code

 ActiveSheet.Range("E1:E" & LR).AutoFilter Field:=1, Criteria1:=Arr, Operator:=xlFilterValues

where Arr is an array of the contents of a range of cells and the filter worked just as requested.

Now, on another project, I wanted to do the same but with this twist...I wanted to filter the range for everything EXCEPT the contents of the array and attempted to use this code

 ActiveSheet.Range("E1:E" & LR).AutoFilter Field:=1, Criteria1:<>Arr, Operator:=xlFilterValues

.

I get a Compile Error "Expected: Named Parameter". So, this doesn't work. Is there a way to use an array to all filter data NOT included in the array?

As always, your help is greatly appreciated.

John

[shg]

That syntax is not valid; it must be namedParameter:=value. I think you'll need to create a second array complementary to the first.

[mikerickson]

Perhaps

...Criteria1:="<>" & Arr

[jaslake]

Hi Mike

This ...Criteria1:="<>" & Arr produces a Type Mismatch error.

John

[jaslake]

Hi shg

The only way that comes to mind to build the complementary array is to do a find and if the value isn't found, add it to the array.

I'm already doing a find and if the value isn't found, I'm adding it to the range. So, I really don't need the array to come into play.

I was hoping for a solution that eliminates the find.

John

[shg]

You can use Advanced Filter to get unique values. Or you could use a UDF that returns a dictionary of unique values, which you could then decimate (quickly) for the values to exclude, and extract the keys as the array. I can give you some code if you like.

[jaslake]

Hi shg

This code is for this thread http://www.excelforum.com/excel-prog...e-dept-id.html

Let me try to explain what's going on then perhaps you can tell me if the ideas you suggested are better, worse or the same as what I'm using.

The OP has raw data that's downloaded from his financial system. He's got 90 departments and wants each department's financial data on a separate worksheet. So, I created a named range of the unique department names called "Depts" and with this named range, the procedure creates a worksheet for each department.

The raw data is then filtered by the named range (Depts) and the filtered data is copied to each individual department worksheet (all 90 of them).

The OP wants all accounts included in the department worksheet even if a given department has no activity for a given account. Now, the OP has 98 accounts. The department worksheets may contain none of the accounts, all the accounts or somewhere in between.

So, I've created a named range called "Accounts" which is a list of all unique account names. I thought, if I could filter this named range (Accounts) to EXCLUDE the accounts that have used in a given department worksheet, I could then just copy the filtered data to that department's worksheet, ergo, the array approach.

What I'm currently doing is a "find". I'm looking at each account name in the named range and "finding it" in the department worksheet. If it doesn't find it, it's added to the department worksheet.

The entire procedure, from start to finish, takes about 20 seconds so the overhead is not so great. But, I thought there might be a more intelligent approach.

I hope I've explained the issue adequately. What I have appears to work (no feedback from OP yet). How do you think your ideas fit?

John