My problem is quite simple, but I haven´t been able to find a solution that works anywhere.
I want to filter out all values in one range that are present in another range - something like this:
SheetA.Range("C1:C" & LastRowA).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=<> & SheetB.Range("B1:B" & LastRow), Unique:=False
However, this does "<>" does not seem to work, as i get a syntax error.
Thanks in advance
As you've discovered, Advanced Filter is only for filtering a range based on the values in another range - not for where they aren't present: https://docs.microsoft.com/en-us/office/vba/api/excel.range.advancedfilter
There are other ways using VBA to achieve this - for example, using autofilter and setting the filter condition. There are plenty of examples on SO that show how to do this though, one being here: https://stackoverflow.com/a/13936244/7858451
Just for fun, to try to get a work around where we absolutely are determined to use Advanced Filter to achieve what you want, there's a bit of a way round it to get the desired result...
If you add an extra column to the right of your data to be filtered, adding a CountIF function there, something like COUNTIF(SheetB!$B$1:$B$100,$C2) (set the ranges accordingly, or better still - used named ranges) to determine whether that value is in the criteria range.
You then set a cell aside somewhere with the value 0 in it and use your advance filter to filter the countIF column for a match on the cell range containing the 0.
This will then filter everything with a countIF of 0.
As I said, this is purely for fun, and not particularly practical - personally I would go down the autofilter route.