Autofilter with more than 2 criteria

[BenNV]

Thanks for the help earlier, Northerner.

Wondering if anyone can help on this one:

Selection.AutoFilter Field:=1, Criteria1:=lowDate, Operator:=xlOr, _
Criteria2:=birthDate, Operator:=xlOr, Criteria3:=highDate

The above does not work because of more than two criteria. What can I do.?

 

[Richie(UK)]

Hi Ben,

As you have discovered, AutoFilter will only accept two criteria for any one filter operation. Depending on how many criteria you have it is possible to use a 'workaround' like this:

Sub FilterCopy() 
Dim rngTarget As Range, rng1 As Range, rng2 As Range, rngMyRange As Range 
Const Crit1 As String = "Crit1" 
Const Crit2 As String = "Crit2" 
Const Crit3 As String = "Crit3" 

Application.ScreenUpdating = False 
With Worksheets("Sheet1") 
    Rows(1).Insert 
    .Range("A1").Value = "dummy" 
    'set up dummy header 
    Set rngTarget = .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row) 
    'set the range to work with 
    rngTarget.AutoFilter Field:=1, Criteria1:=Crit1, Operator:=xlOr, Criteria2:=Crit2 
    'get first two criteria 
    Rows(1).Hidden = True 
    'hide dummy header 
    Set rng1 = rngTarget.SpecialCells(xlCellTypeVisible) 
    'range with first 2 
    rngTarget.AutoFilter 
    'reset autofilter 
    rngTarget.AutoFilter Field:=1, Criteria1:=Crit3 
    'get third criteria 
    Rows(1).Hidden = True 
    'hide dummy header 
    Set rng2 = rngTarget.SpecialCells(xlCellTypeVisible) 
    'range with 3rd 
    rngTarget.AutoFilter 
    Set rngMyRange = Union(rng1, rng2) 
    'combine the range 
    rngMyRange.EntireRow.Copy _ 
        Destination:=Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0) 
    'or whatever you want to do ... 
End With 
Application.ScreenUpdating = True 

End Sub

However, just because you can do it doesn't mean you should :wink: What is your objective? Have you tried using the AdvancedFilter?

 

[BenNV]

Thanks Ritchie.

My objective is for the user to enter a date (birthDate) and the program to return the day before(lowDate) and day after (highDate)

Selection.AutoFilter Field:=1, Criteria1:=lowDate, Operator:=xlOr, _
Criteria2:=birthDate, Operator:=xlOr, Criteria3:=highDate

 

[BenNV]

Ritchie:

I could use the following:

Selection.AutoFilter Field:=7, Criteria1:=>=lowDate, Operator:=xlAnd _
, Criteria2:=<=highDate

But I get an error, where could I put the " "?