Multiple Filter for a Column

[rkunal]

Hi,

I am trying to add multiple filter in a column using macros.

For Example: In a column, I want to filter cells containing apple and orange but not banana or guava. So there are 4 conditions.

For 2 conditions, I am able to create below code but don't know how to extend this to satisfy all conditions.

Sub Macro4()'' Macro4 Macro'
'    ActiveSheet.Range("$A:$A").AutoFilter Field:=1, Criteria1:="=*apple*" _        , Operator:=xlAnd, Criteria2:="<>*banana*"End Sub

I am new to macros so please help me with this. Thanks in advance.

--
Kunal

 

[SupremeDr]

Hi,

I am trying to add multiple filter in a column using macros.

For Example: In a column, I want to filter cells containing apple and orange but not banana or guava. So there are 4 conditions.

For 2 conditions, I am able to create below code but don't know how to extend this to satisfy all conditions.

Sub Macro4()'' Macro4 Macro'
'    ActiveSheet.Range("$A:$A").AutoFilter Field:=1, Criteria1:="=*apple*" _        , Operator:=xlAnd, Criteria2:="<>*banana*"End Sub

I am new to macros so please help me with this. Thanks in advance.

--
Kunal

You say theres 4 condition but it looks like there's only 2.

You want to see Only apple orange so change Criteria2 to ="=*Orange*"

This will show only those 2. If you are filtering a pivot table it is a little different.

 

[rkunal]

Thanks for replying. I have added an example for better understanding of my criteria.

*My criteria: The cell should contain either apple OR orange but should not contain either banana OR guava.

Values	Result after filter
Apple	Apple
Apple Orange	Apple Orange
Apple Banana	Orange
Apple Guava	Orange Apple
Orange
Orange Apple
Orange Banana
Orange Guava
Banana
Banana Apple
Banana Orange
Banana Guava
Guava
Guava Apple
Guava Orange
Guava Banana

<tbody>
</tbody>

 

[SupremeDr]

Thanks for replying. I have added an example for better understanding of my criteria.

*My criteria: The cell should contain either apple OR orange but should not contain either banana OR guava.

Values	Result after filter
Apple	Apple
Apple Orange	Apple Orange
Apple Banana	Orange
Apple Guava	Orange Apple
Orange
Orange Apple
Orange Banana
Orange Guava
Banana
Banana Apple
Banana Orange
Banana Guava
Guava
Guava Apple
Guava Orange
Guava Banana

<tbody>
</tbody>

Can you filter by Apple, Apple Orange, Orange, and Orange Apple? Or is your data more complex then that?

 

[DanteAmor]

Try this

Sub Multiple_Filter()
   Dim c As Range, Rng As Range, i As Long, j As Long
   Dim inis() As Variant, outs() As Variant, exists As Boolean
   inis = Array("apple", "orange", "lemon") [B][COLOR=#008000]'should contain[/COLOR][/B]
   outs = Array("banana", "guava", "melon") [B][COLOR=#ff0000]'but should not contain[/COLOR][/B]
   
   With ActiveSheet
      Set Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
      ReDim ary(Rng.Count)
      For Each c In Rng
        exists = False
        For j = 0 To UBound(inis)
          If InStr(1, LCase(c), LCase(inis(j))) > 0 Then
            exists = True
            Exit For
          End If
        Next
        For j = 0 To UBound(outs)
          If InStr(1, LCase(c), LCase(outs(j))) > 0 Then
            exists = False
            Exit For
          End If
        Next
        If exists Then
          ary(i) = c.Value
          i = i + 1
        End If
      Next c
      .Range("A:A").AutoFilter 1, ary, xlFilterValues
   End With
End Sub