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.

[TABLE="class: grid, width: 336"]
<tbody>[TR]
[TD]Values[/TD]
[TD]Result after filter[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Apple Orange[/TD]
[TD]Apple Orange[/TD]
[/TR]
[TR]
[TD]Apple Banana[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Apple Guava[/TD]
[TD]Orange Apple[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange Apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange Guava[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana Apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana Orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana Guava[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Guava[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Guava Apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Guava Orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Guava Banana[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="class: grid, width: 336"]
<tbody>[TR]
[TD]Values[/TD]
[TD]Result after filter[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Apple Orange[/TD]
[TD]Apple Orange[/TD]
[/TR]
[TR]
[TD]Apple Banana[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Apple Guava[/TD]
[TD]Orange Apple[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange Apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange Guava[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana Apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana Orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana Guava[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Guava[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Guava Apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Guava Orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Guava Banana[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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