autofilter dynamic criteria

[jenyaUtd]

Hello all,
I want to Create a sub of autofilter in VBA that will autofilter my range by a criteria that is written somewhere in my sheet,

for example:
I have a table:

code|Pname|price|
1 aaa 10
2 bbb 11
3 ccc 12
4
...
...
...

and in cell A1,A2,A3 a list of Cedes 1,2,3
and the filter will pick the values in those cells as a criteria for the auto filter

many thanks

 

[Yongle]

Welcome to the forum

I have a table

- is this an Excel Table created with Insert \ Table ?

and in cell A1,A2,A3 a list of Codes 1,2,3
and the filter will pick the values in those cells as a criteria for the auto filter

- assumed that required filter is ALL values in the list (not separately on individual values)

In code below the data is assumed to be in a structured Excel Table in Sheet1 with criteria is in Sheet2 column A (with header in A1)

Sub FilterTable()
    Dim Rng As Range, Cel As Range, Arr() As String, c As Long
[COLOR=#006400][I]'criteria range[/I][/COLOR]
    With Sheets("[COLOR=#ff0000][I]Sheet2[/I][/COLOR]")
        Set Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With
[COLOR=#006400][I]'load values into an array[/I][/COLOR]
    c = 0
    For Each Cel In Rng
        If Cel <> "" Then
            ReDim Preserve Arr(c)
            Arr(c) = Cel.Text
            c = c + 1
        End If
    Next
[COLOR=#006400][I]'clear prior filters & apply new filter[/I][/COLOR]
    With Sheets("[I][COLOR=#ff0000]Sheet1[/COLOR][/I]").ListObjects("[COLOR=#ff0000][I]TableName[/I][/COLOR]")
        .ShowAutoFilter = False
        .ShowAutoFilter = True
        .DataBodyRange.AutoFilter Field:=1, Criteria1:=Arr, Operator:=xlFilterValues
    End With
End Sub

If the data is in a range rather than a structured table, replace the last bit of code
- the data is assumed to be in columns A:C in Sheet1 with headers in row 1

[COLOR=#006400][I]'clear prior filters & apply new filter[/I][/COLOR]
    With Sheets("Sheet1")
        On Error Resume Next
        .ShowAllData
        .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=Arr, Operator:=xlFilterValues
    End With

 

[jenyaUtd]

Works perfectly,
Many TNX!!!!!!!

 

[Yongle]

Thanks for the feedback

 

[jenyaUtd]

Sorry for the previous reply I've try the code in my work where I truly need it and it doesn't work, I guess is because the filler I try to apply is on a field of a pivot table. The PT is a BI system that I have in my work from where I get my data for my reports

 

[Yongle]

There is another area in this forum for BI tools
- I suggest you post your question there, explaning exactly which BI too;s you are using etc

 

[jenyaUtd]

Did you mean "power bi"?

 

[Yongle]

Whether you place your question there or in this area you are more likely to encourage the most appropriate members to help if you start a new thread with a new title (perhaps including the words pivot table dynamic filters ) and a full description of exactly what the problem is
Good luck