Clever Geek Handbook

VBA Excel: filter with "or" condition between two columns

I have 2 columns (A and D) in my 10 column sheet (AJ) with two value types, "OK" or "NOT OK". I want the filter to have all rows with at least one "NOT OK". I also want the filter (in the header, which is line (1)) to appear automatically after running the macro, so that I can use it later. However, my code is not working.

with wb1.Sheets(3)
    .Range("A1:J1").AutoFilter
    .Range("A1:J1").AutoFilter Field:=1, Criteria1:="NOT OK", _ Operator:=xlOr, Field:=4, Criteria1:="NOT OK"
End with

Is it doable? Thank you in advance.

+3


source to share


2 answers


The best way to do this is to add a new column with a formula that checks all your requirements, after which you can filter this new column.



+1


source


Advanced filters can set complex criteria. We can temporarily use a hosted filter range and then clear it.

With wb.Worksheets("Sheet3")
  .Range("AA1:AB1").value = Array("ColA", "ColJ")
  .Range("AA2:AB2").value = Array("NOT OK", "")
  .Range("AA3:AB3").value = Array("", "NOT OK")

  .Columns("A:J").AdvancedFilter xlFilterInPlace, .Range("AA1:AB3")
  .Range("AA1:AB3").ClearContents ' clear it after dome with it
End With


The same idea can be applied with a regular Autofilter

though, using temporarily some unused column to generate the filter condition as boolean. For example using column AA

(= 27)

With wb.Worksheets("Sheet3")
  .Range("AA2:AA1000").Formula = "=OR(A2=""NOT OK"", J2=""NOT OK"")"
  .Range("A1:AA1000").AutoFilter 27, True
  .Columns("AA").Delete xlToLeft
End With
0


source






More articles:


All Articles