autofilter dynamic criteria

jenyaUtd

New Member
Joined
Jan 26, 2019
Messages
11
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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)
Code:
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
Code:
[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
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top