AdvancedFilterCopy with criteria <>something

AlanofBayCourt

New Member
Joined
Oct 15, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
I think I know how to put criteria, data and output range on a sheet like this:-
(Credit to Paul Kelly for the illustration)

Advanced-filter-ranges.png

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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,110
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you're using AdvancedFilter in VBA, you still need a criteria range, just the same as when you do it manually.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,197
Office Version
  1. 2007
Platform
  1. Windows
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:
Excel 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.

Rich (BB code):
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

New Member
Joined
Oct 15, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,405
Messages
5,769,881
Members
425,578
Latest member
Ckrysa

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
Top