Criteria when coding autofilter

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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:
Code:
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

New Member
Joined
Dec 2, 2009
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,745
Messages
5,626,624
Members
416,195
Latest member
tonmcg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top