RE: VBA filter by 3rd criteria

[philhowell]

RE: VBA filter by 3rd criteria

How can i filter three criteria's.........

this does two....... :D
Selection.AutoFilter Field:=6, _
Criteria1:="<>A*", Operator:=xlAnd, _
Criteria2:="<>W*", Operator:=xlAnd

but this doesn't work........
Selection.AutoFilter Field:=6, _
Criteria1:="<>A*", Operator:=xlAnd, _
Criteria2:="<>W*", Operator:=xlAnd
Criteria2:="<>Q*"

 

[Seti]

You have Criteria2 listed twice. Is this a typo on your post or in your code?

 

[Richie(UK)]

Hi Phil,

The AutoFilter will only accept two criteria.

As a workaround, perform your first filter with 2 criteria and set a range variable equal to the visible cells. Then show all data and filter again with the third criteria and set another range variable to these visible cells. Then define your final range as the union of these other two ranges.

HTH

 

[philhowell]

Sorry, criteria2 used twice was a typo (i jus' copied and pasted the second line)........ changing it doen't help tho

Richie, can you give me an example snippet of code........

cheers
phil

 

[Richie(UK)]

Hi Phil,

Something 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

HTH