Autofilter on multiple columns with unrelated criteria

user_7862279 0
Viewed 226

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.

autofilter vba excel
3 Answers

I think something like using AdvancedFilter

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

enter image description here

user_14440970

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.

user_32196130

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.

user_116061930
Related