Select data using autofilter which doesn't satisfy given criteria

[Muhamed Faizal]

I still face problem with AutoFilter

This time my issue is, I want auto filter to show all the data which doesn’t match with given criteria.

following code brings the data which satisfy the criteria but my requirement is directly opposite. I'm not sure where to use NOT in the given code

ActiveSheet.Range("$A$1:$D$14").AutoFilter Field:=1, Criteria1:=Array("MRP - *", "WAP - *"), Operator:=xlFilterValues

Thanks in Advance</SPAN>

 

[rallcorn]

Did you try using the macro recorder? You'd get this:

Range("A1:A14").AutoFilter
ActiveSheet.Range("$A$1:$A$14").AutoFilter Field:=1, Criteria1:= _
"<>*MRP -**", Operator:=xlAnd, Criteria2:="<>*WAP -**"

 

[Muhamed Faizal]

every time, data will be different, macro recorder won't work in this case

 

[rallcorn]

every time, data will be different, macro recorder won't work in this case​

Right, but it does give you the syntax it is looking for, which is <> rather than "Not" - you had said:

I'm not sure where to use NOT in the given code

You are not using NOT, you're using <> - which you learned from the macro recorder!

 

[Muhamed Faizal]

Rallcorn,

Your code works only when the criteria has two conditions
ActiveSheet.Range("$A$1:$D$19").AutoFilter Field:=1, Criteria1:="<>*QC*", Operator:=xlAnd, Criteria2:="<>*Test*"

when using macro filter, I get following code

ActiveSheet.Range("$A$1:$D$19").AutoFilter Field:=1, Criteria1:=Array("MKT - Global Mailing Addresses", "MRP - Enrollment Report", _
"MRP - KPMG TaxWatch Repair Regs Webcast Attendees Report", "MRP - Local Event Attendee Analysis Report", _
"MRP - Marketing List Vetting Report", "MRP - MPP Account Team Leadership", "Prep"), Operator:=xlFilterValues


Every time I cannot insert the details like macro recorder captures

what I'm looking is, is there a way i can use "<>" in the arrary
</SPAN>

Following is my Sample Data
[TABLE="width: 489"]
<TBODY>[TR]
[TD]Row Labels</SPAN>
[/TD]
[TD]Time </SPAN>
[/TD]
[/TR]
[TR]
[TD]Prep</SPAN>
[/TD]
[TD="align: right"]91.667</SPAN>
[/TD]
[/TR]
[TR]
[TD]MKT - Global Mailing Addresses</SPAN>
[/TD]
[TD="align: right"]68.167</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - Enrollment Report</SPAN>
[/TD]
[TD="align: right"]7.500</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - KPMG TaxWatch Repair Regs Webcast Attendees Report</SPAN>
[/TD]
[TD="align: right"]3.500</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - Local Event Attendee Analysis Report</SPAN>
[/TD]
[TD="align: right"]4.000</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - Marketing List Vetting Report</SPAN>
[/TD]
[TD="align: right"]4.000</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - MPP Account Team Leadership</SPAN>
[/TD]
[TD="align: right"]2.000</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - MPP Weekly Webcast Report</SPAN>
[/TD]
[TD="align: right"]0.500</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - Webcast Attendee Analysis Report</SPAN>
[/TD]
[TD="align: right"]2.000</SPAN>
[/TD]
[/TR]
[TR]
[TD]QC</SPAN>
[/TD]
[TD="align: right"]48.333</SPAN>
[/TD]
[/TR]
[TR]
[TD]Test</SPAN>
[/TD]
[TD="align: right"]25.833</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - Enrollment Report</SPAN>
[/TD]
[TD="align: right"]6.833</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - KPMG TaxWatch Repair Regs Webcast Attendees Report</SPAN>
[/TD]
[TD="align: right"]3.500</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - Local Event Attendee Analysis Report</SPAN>
[/TD]
[TD="align: right"]4.000</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - Marketing List Vetting Report</SPAN>
[/TD]
[TD="align: right"]3.667</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - MPP Account Team Leadership</SPAN>
[/TD]
[TD="align: right"]2.000</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - MPP Weekly Webcast Report</SPAN>
[/TD]
[TD="align: right"]0.500</SPAN>
[/TD]
[/TR]
[TR]
[TD]MRP - Webcast Attendee Analysis Report</SPAN>
[/TD]
[TD="align: right"]2.000</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[rallcorn]

OK - did a bit of reading & apparently an array of multiple criteria can be used when for EQUAL but cannot be used for does NOT equal.

Some suggested exploring "Advanced" filter - but that would require you to create a list of the items you DO want to show.