VBA AutoFilter: multiple criteria; array; does not contain

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
224
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.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

nikio8

Board Regular
Joined
Oct 20, 2017
Messages
128
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

Board Regular
Joined
Jul 19, 2016
Messages
224
Office Version
  1. 365
Platform
  1. Windows

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
224
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,929
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,118,861
Messages
5,574,709
Members
412,613
Latest member
EFRATA
Top