excelvbaadvanced-filter

AdvancedFitler Out Values from ListRange using Formula

I'm trying to setup AdvancedFilter to filter out a ListRange of items. After some testing, I realized that it only accepts using a "formula" of <>A when I use a criteria range of 2 cells. If I add a third <>B it just does nothing.

My original thought was simple to prepend to my column <> to each cells value, but now it seems that won't work. I need to figure out a way to have both a formula and a range somehow applied.

IE:

Data:

Let Num
A   1
B   2
C   3

This Works for Filter Range:

Let
<>B

This Doesn't:

Let
<>B
<>C

But my CriteriaRng looks like this:

Let
B
C

How I can reference a way to say for all items in Let column, Filter <>Cell.Value in CriteriaRange:=

Here's the basic code I tried/debugged this issue with:

 FilterRng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("D1:D3"), Unique:=False
 Stop
 FilterRng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("D1:D2"), Unique:=False
 Stop

Updates:

I found this example --> https://www.mrexcel.com/board/threads/with-adavnced-filter-how-do-i-exclude-a-value.733153/page-2

=ISNA(MATCH($A9,Exclude!$A$1:$A$2,0))

But I'd need to built that formula via VBA and make it much more generic. I'm better w/ VBA then formula's.

I also read in this post that he basically uses highlighting via regular filter, then another filter based on highlighting, but I know there is a better way utilizing a formula in a cell.

https://stackoverflow.com/a/34012365/5079799

I think I also somewhere you can do "or" operations when staggering rows w/ advanced filter, so I could make my column a staggered column, but that also sounds hacky and I couldn't get it to work on my brief attempt.

Solution

  • I basically copied my answer from this one, but built the FormulaStr and automated it more, as thats the point of VBA!

    https://stackoverflow.com/a/28506854/5079799

    Sub Test()

Dim ws As Worksheet
Set ws = ActiveSheet

Dim CriteriaRng As Range, DataRng As Range
Set CriteriaRng = ws.Range("D1:D3")
Set DataRng = ws.Range("A1:B4")

Dim FormulaRng As Range, FormulaStr As String, DataRngCellTwoStr As Range
Set DataRngCellTwoStr = Cells(DataRng.Row + 1, DataRng.Column)
Set FormulaRng = ws.Range(Cells(2, CriteriaRng.Column + 1), Cells(2, CriteriaRng.Column + 1))
FormulaStr = "=ISNA(MATCH(" & DataRngCellTwoStr.Address(False, False) & "," & CriteriaRng.Address & ",0))"
FormulaRng.Value = FormulaStr
ws.Range(Cells(1, CriteriaRng.Column + 1), Cells(1, CriteriaRng.Column + 1)).Clear
Set FormulaRng = ws.Range(Cells(1, CriteriaRng.Column + 1), Cells(2, CriteriaRng.Column + 1))

DataRng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=FormulaRng, Unique:=False

End Sub

    Notes: