VBA AutoFilter: multiple criteria; array; does not contain

dougmarkham

Active Member
Joined
Jul 19, 2016
Messages
252
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
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":
Code:
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:

Code:
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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(
 
Upvote 0
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top