CODEWITHSUNDEEP
excelvbaautofilter
Jack
Jack

Reputation: 733

Autofilter on multiple columns with unrelated criteria

I'm trying to filter my data in VBA. The data looks like this:

enter image description here

I want to only display the lines with FAIL written on it. So I have tried .ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="FAIL" .ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:="=FAIL"

However it just hides everything. If the data was in the same column I would've used the operator attribute but since it is in different columns I don't really know what to do.

So is it possible to filter unrelated data from different columns?

Thank you in advance.

Upvotes: 4

Views: 349

Answers (3)

Deepstop
Deepstop

Reputation: 3827

I don't think so. Filters look like they operate on one column only. When you use the interface to define a filter it only offers a way to set criteria on a single column.

Filter one will hide all rows that don't have FAIL in the first column, and filter two will hide the ones that don't have FAIL in the second. So unless FAIL is in both columns the row will be hidden.

You could use an additional column to accomplish it. Say you add column C, and the ones above are A & B. A formula =OR(A2="FAIL", B2="FAIL"). Then filter on C = TRUE.

Upvotes: 1

Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

I think something like using AdvancedFilter

Range("A1:B4").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
        ("D1:E3"), Unique:=False

enter image description here

Upvotes: 2

Pᴇʜ
Pᴇʜ

Reputation: 57753

Filters are using AND related criteria in multiple columns but what you need is an OR related criteria (which is not supported):

So you can use a helper column with COUNTIF

enter image description here

Eg the following formula in C2 (and copy it down):

=COUNTIF(A2:B2,"FAIL")

And then filter the helper column for >=1.

Upvotes: 2

Related Questions