Criteria when coding autofilter

[staticbob]

Hi,

I am trying to use VBA to set auto-filters, it works with 2, but not 3 criteria ???

This is my code . .

Selection.autofilter Field:=11, Criteria1:="=G", Operator:=xlOr, _
Criteria2:="=H", Operator:=xlOr, Criteria3:="=I"

Any ideas ?

Bob

 

[Mudface]

You only have two criteria available when using Autofilter. You'd need to use an Advanced Filter for more criteria. Take a look at the Help entry for Advanced Filter and record your actions for more details.

 

[Richie(UK)]

Hi,

Either use the Advanced Filter, as Mudface has suggested, or cheat! What I mean is you can, if you really want to, repeat the AutoFilter action and use Union to combine the ranges. 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 rngTarget = .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
        rngTarget.AutoFilter Field:=1, Criteria1:=Crit1, Operator:=xlOr, Criteria2:=Crit2
        'get first two criteria
        Rows(1).Hidden = True
        Set rng1 = rngTarget.SpecialCells(xlCellTypeVisible)
        rngTarget.AutoFilter
        rngTarget.AutoFilter Field:=1, Criteria1:=Crit3
        'get third criteria
        Rows(1).Hidden = True
        Set rng2 = rngTarget.SpecialCells(xlCellTypeVisible)
        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

HTH

 

[srivardhan09]

Dear All,

Can you please tell me what is wrong in this code

Private Sub CommandButton1_Click()


If CheckBox1.Value = True Then
Range("a1:b7").AutoFilter Field:=1, Criteria1:="A"

GoTo jump1
Else
If CheckBox1.Value = False Then
GoTo jump1
jump1:
If CheckBox2.Value = True Then
Union(Selection, Range("a1:b7")).AutoFilter Field:=1, Criteria1:="B"

GoTo jump2
Else
If CheckBox2.Value = False Then
GoTo jump2
jump2:
If CheckBox3.Value = True Then
Union(Selection, Range("a1:b7")).AutoFilter Field:=1, Criteria1:="C"


End If
End If
End If
End If
End If
End Sub

Regards,
Srivardhan