AdvancedFilterCopy with criteria <>something

[AlanofBayCourt]

I think I know how to put criteria, data and output range on a sheet like this:-
(Credit to Paul Kelly for the illustration)

and if you edit A2 to contain <>Orange you will get everything but Oranges in the Copy To range.

I also almost understand how to use VBA to setup and run AvancedFilterCopy macros.
It all works fine except I have no idea how to replicate the setting of criteria to be not equal to something like <>Orange. I need to filter a large data sheet that has supplier codes in a column. I want to get two new sheets. One filtered to show all rows with a Supplier Code that matches a criteria. And one that shows all rows where the Supplier Code is different to the Criteria. ie <> SupplierCode.
The first sheet is easy and the second sheet works with a macro with a criteria referring to a cell in which I have typed <>SupplierCode. BUT I just can't see how to do it with VBA. Any ideas please.

 

[RoryA]

If you're using AdvancedFilter in VBA, you still need a criteria range, just the same as when you do it manually.

 

[DanteAmor]

Hi and welcome to MrExcel.

It all works fine except I have no idea how to replicate the setting of criteria to be not equal to something like <>Orange.

Continuing with your example, in cell B2 put the formula:

="<>Orange"

BUT I just can't see how to do it with VBA.

The following is an example to have the data for the advanced filter in 3 different sheets. And as RoryA comments, the criteria must be in the cells, just like you do it manually.

Sub Macro2()
  Sheets("Origen").Range("A4:C11").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("sh_criteria").Range("A1:C2"), _
    CopyToRange:=Sheets("Destination").Range("E4:G4"), _
    Unique:=False
End Sub

 

[AlanofBayCourt]

Thanks for your replies but they were not what I was hoping for. I finally came up with the following work round, I added another column which was based on my original data column but concatenated with "<>" as a prefix.
So Column B contains the Supplier Codes and column G contains the formula
="<>"&$B
Using G as the criteria column gets the results I need manually or with VBA

Hope this helps all the other people googling for "VBA AdvancedFilter Criteria NOT equal to"

If you agree please mark this post as Solved.