AutoFilter with 3 Criterias

RaVon · Apr 2nd 2004

Hi guys..
Need some help here...
I'm trying to have 3 criterias in 1 autofilter column but it doesnt seems to work..

This line is working fine...
Selection.AutoFilter Field:=18, Criteria1:="Current", Operator:=xlOr, Criteria2:="Past Due"

But I need to add another criteria...
Selection.AutoFilter Field:=18, Criteria1:="Current", Operator:=xlOr, Criteria2:="Past Due" , Operator:=xlOr, Criteria3:="Complete"

Why is that so?
I realise that the custom filter option only allows 2 criterias, is that the cause of it?

Thanks in advance...
:(:(

Richie(UK) · Apr 2nd 2004

Hi RaVon,

You can only use two criteria because that is all that AutoFilter will accept. You can 'cheat' in VBA by repeating autofilter calls and using the Union method to merge the respective ranges.

For example:

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 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

Display More

HTH

RaVon · Apr 2nd 2004

thanks i see what u mean..
will try alternatives...

Dave Hawley · Apr 2nd 2004

Hi RaVon

Advanced Filter can take up to 256 criteria

RaVon · Apr 2nd 2004

Hi there...
How can i implement Advance Filter through codes?
Interested to learn.. :yes:

Quote

Originally posted by Dave Hawley
Hi RaVon

Advanced Filter can take up to 256 criteria

Dave Hawley · Apr 2nd 2004

Probaly best to read up in the Excel VBA help. Look under "AdvancedFilter Method" post back with any specifics.

Anonymous · Apr 2nd 2004

The advanced filter is easily done in VBA. As with many such things it's probably easiest to turn on the macro recorder and first do it manually, then clean up the resulting code.

AutoFilter with 3 Criterias

Participate now!

Share

Users Viewing This Thread