VBA AutoFilter: multiple criteria; array; does not contain

[dougmarkham]

Hi Folks,

Firstly: all this is for a dynamic table called StockListTable.

I have a Column (Header = Owner) containing numbers representing customer numbers (1 to 150 plus 998 and 999).
I wish to filter out the following customer numbers---0, 27, 72, 84, 100, 998 and 999---thus leaving all other customer numbers.

I can use autofilter to include all customer numbers using an array:

ActiveSheet.Range("StockListTable[Owner]").AutoFilter Field:=4, Criteria1:=[COLOR=#0000cd][B]Array("1", "2", "n", etc)[/B][/COLOR], Operator:=xlFilterValues

However, I can only include about 150 values in the array and no more; hence, as customers are added, this option will fail.

I have tried to use an array to specify "Does not contain":

ActiveSheet.Range("StockListTable[Owner]").AutoFilter Field:=4, Criteria1:=[COLOR=#0000ff][B]Array("<>0", "<>27", "<>72", etc)[/B][/COLOR], Operator:=xlFilterValues

However, VBAeditor gives the error message: Run-time error: '2': Subscript out of range.
I have tried an alternative way of filtering using arrays:

Sub ArrayFilter()


Dim lo As Excel.ListObject
Dim CustNumbers As Variant


Set lo = ActiveWorkbook.Worksheets("Stock List").ListObjects("StockListTable")
CustNumbers = [COLOR=#0000ff][B]Array("<>0", "<>027", "<>72")[/B][/COLOR]
lo.DataBodyRange.AutoFilter Field:=4, _
    Criteria1:=CustNumbers, Operator:=xlFilterValues
End Sub

VBeditor outputs this error: Run-time error '1004': AutoFilter method or Range class failed.

Would anybody be willing to help me modify the code to allow it to filter out multiple customer numbers?

Kind regards,

Doug.

 

[Fluff]

Autofilter only accepts 2 values unless you are filtering on exact matches.
You either need to create an array of all the matches you want, or have a look at using Advanced filters instead. https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/

 

[nikio8]

I tried something similar the other day. Filter all zeros. Rather than printing 10 pages, only print 1.
"<>0", would not work, but ">0" does. I did not get further than that.
Perhaps you can add another column, IF(OR(

 

[dougmarkham]

Autofilter only accepts 2 values unless you are filtering on exact matches.
You either need to create an array of all the matches you want, or have a look at using Advanced filters instead. https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/

Hi Fluff,

Thanks for the link, this will be useful for sure going forward!

Kind regards,

Doug.

 

[dougmarkham]

I tried something similar the other day. Filter all zeros. Rather than printing 10 pages, only print 1.
"<>0", would not work, but ">0" does. I did not get further than that.
Perhaps you can add another column, IF(OR(

Hi Nikio8 & Fluff,

I think given the limitations of the program and the length of the dataset, I think I'll program a find & replace macro to change the customer numbers to one value so I can filter on that.
Thanks for your help!

Kind regards,

Doug.

 

[Fluff]

You're welcome & thanks for the feedback