multiple filter selection using VBA macro

[mobeenraheem]

Hi

I am trying to achieve multiple filter selection using VBA macro but its not working as it should.

Below macro is working fine when there is two values.its not working more then two values.
Can please someone tell me what i am missing.

Sub Macro1()
'
' Macro1 Macro
'


'
    Columns("A:A").Select
    Selection.AutoFilter
    Range("A1").Select
    ActiveSheet.Range("$A$1:$A$11076").AutoFilter Field:=1, Criteria1:=Array( _
        "=*value1)*", "=*value2*", "=*value3*", "=*value4*"), Operator:=xlFilterValues
End Sub

 

[venkat1926]

suppose data is like this

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]hdng1[/TD]
[TD="class: xl66, width: 64"]hdng2[/TD]
[/TR]
[TR]
[TD="class: xl67"]aa[/TD]
[TD="class: xl68, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67"]as[/TD]
[TD="class: xl68, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl67"]dd[/TD]
[TD="class: xl68, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67"]fg[/TD]
[TD="class: xl68, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl67"]gh[/TD]
[TD="class: xl68, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl67"]as[/TD]
[TD="class: xl68, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl67"]sd[/TD]
[TD="class: xl68, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl67"]df[/TD]
[TD="class: xl68, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl67"]fg[/TD]
[TD="class: xl68, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl67"]gh[/TD]
[TD="class: xl68, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl67"]a[/TD]
[TD="class: xl68, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl67"]hg[/TD]
[TD="class: xl68, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl67"]gf[/TD]
[TD="class: xl68, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl67"]fg[/TD]
[TD="class: xl68, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl69"]jg[/TD]
[TD="class: xl70, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]




somehow more tha two criteria does not work even using array function. experts may give better explanation. I subvert this by this macro. try that and use it by modifying.see variable x(1),x(2) and x(3)
the result of macro is in sheet2

Sub test()
Dim x(1 To 3), j As Integer
Dim r As Range
x(1) = "a*"
x(2) = "s*"
x(3) = "d*"
Worksheets("sheet2").Cells.Clear
Worksheets("sheet1").Activate
Set r = Range("A1").CurrentRegion


r.AutoFilter field:=1, Criteria1:=Array(x(1), x(2)), Operator:=xlFilterValues
r.Offset(1, 0).Resize(r.Rows.Count - 1).Copy Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    
     ActiveSheet.AutoFilterMode = False
     r.AutoFilter field:=1, Criteria1:=x(3)
    r.Offset(1, 0).Resize(r.Rows.Count - 1).Copy Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
     ActiveSheet.AutoFilterMode = False
MsgBox "macro done see sheete2"
End Sub

 

[Peter_SSs]

Below macro is working fine when there is two values.its not working more then two values.
Can please someone tell me what i am missing.

Using AutoFilter with Text Filters as you are trying to do here only allows two values - It is just an Excel limit.
If you manually try AutoFilter in the sheet and choose 'Text Filters' from the drop-down, you only get the choice of two, whether it be "Equals", "Begins with", "Contains" etc.