Macro autofilter Or/And operator

[Tiobyte]

Hi all,

I am currently using autofilter in a macro to move data over that = certain text.

With Sheets("Data")      
       If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:O1").AutoFilter 6, "Test1"
      .Range("A1:O1").AutoFilter 2, "Test2"
      .Range("A1:O1").AutoFilter 3, "Test3"
      .AutoFilter.Range.Offset(1).Copy Sheets("Results").Range("A2")
      .AutoFilterMode = False
    End With

I now need to be able to say if column 6 = Test1 OR column 2 = Test2 OR column 3 = Test3.

I know how to do this with the or operator when its looking in the same column each time but not with different columns. All suggestions welcome thanks for your help.

 

[jmacleary]

If you can add another column to the sheet then you could use a formula such as =IF(OR(B1="Test2",C1="Test3",F1="Test1"),"Yes","No") then filter that column on "Yes".

 

[Tiobyte]

If you can add another column to the sheet then you could use a formula such as =IF(OR(B1="Test2",C1="Test3",F1="Test1"),"Yes","No") then filter that column on "Yes".

Unfortunately this way wont work for my scenario but thanks for the input.

 

[Tiobyte]

Tried the following code however it only applies the first condition not the or part, any ideas?

   With Sheets("Sheet1")      
       If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:O1").AutoFilter 1, "Test1", Operator:=xlOr
      .Range("A1:O1").AutoFilter 6, "Test2"
      .AutoFilter.Range.Offset(1).Copy Sheets("Sheet2").Range("A2")
      .AutoFilterMode = False
   End With

 

[Fluff]

Have a look at advanced filters http://www.contextures.com/exceladvancedfiltervba.html

 

[Tiobyte]

Have a look at advanced filters http://www.contextures.com/exceladvancedfiltervba.html

Thanks for the link, i came across this earlier and downloaded the sample file given. I tried recording a macro using the example filters with or/and functions to see what the VBA code would look like. From there i have unfortunately not been able to apply this to my code and get it to work but i will keep trying.

 

[Peter_SSs]

I tried recording a macro using the example filters with or/and functions to see what the VBA code would look like. From there i have unfortunately not been able to apply this to my code and get it to work but i will keep trying.

Assuming column Z on the "Data" sheet is available as a helper column (if not substitute another column) then try this with a copy of your workbook.

Sub FilterAndCopy()
  Dim rCrit As Range
  
  With Sheets("Data")
    If .AutoFilterMode Then .AutoFilterMode = False
    Set rCrit = .Range("Z1:Z2")
    rCrit.Cells(2).Formula = "=OR(F2=""Test1"",B2=""Test2"",C2=""Test3"")"
    .Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=Sheets("Results").Range("A2"), Unique:=False
    rCrit.ClearContents
  End With
  Sheets("Results").Rows(2).Delete
End Sub