VBA Autofilter to filter using 2 columns

[mecerrato]

I have 2 columns that I would like to filter by but need the results to show for both criteria.

ActiveSheet.Range("$a$10:$cp$500").AutoFilter Field:=19, Criteria1:="Red"
ActiveSheet.Range("$a$10:$cp$500").AutoFilter Field:=89, Criteria1:="Red"

So if either column S (filed #19) or column CK (filed #89) are equal to Red I want to see them

Anyone know how to solve?

 

[Fluff]

You could create a helper column in col CQ with this formula

=OR(S11="Red",CK11="Red")

filled down & then filter that column for TRUE.

 

[Marc L]

​

Or use an advanced filter …​

 

[mecerrato]

​

Or use an advanced filter …​

I don't want to use a helper column, this is what I have but it only gives me no results, is there such thing as an OR operator I can use?

Sub Filter_by_Red_CD_LE()
ActiveSheet.Unprotect
Dim ddate As String
ddate = Month(Date)
Dim r As Range

Set r = ActiveSheet.Range("$a$10:$cp$500")
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
    Selection.AutoFilter
    ActiveSheet.Range("$a$10:$cp$500").AutoFilter Field:=19, Criteria1:="Red"
    ActiveSheet.Range("$a$10:$cp$500").AutoFilter Field:=89, Criteria1:="Red"
    Range(Cells(r.Rows.Count + 1, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True
    Range("$a$10:$cp$500").Sort Key1:=Range("B10"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollRow = 11
Range("A8").Value = "Current Filter = REd LE/CD"
Worksheets("Pipeline").Shapes("Drop Down 11").ControlFormat.Value = 0

End Sub

 

[Marc L]

​

Not need any helper column and the advanced filter is an easy way …​

 

[Fluff]

I don't want to use a helper column,

Then you can't use autofilter.

 

[mecerrato]

​

Not need any helper column and the advanced filter is an easy way …​

Ok, can you help me with the code?

 

[Marc L]

​

As it may depend on how smart is the worksheet design I need at least an attachment …​