Excel VBA Dynamic filters

Question

I am trying to create dynamic filters based on varying conditions and different criteria. Suppose the user is providing some data like

Sal>100 and sal<1000 and not equal to 500

I am dynamically able to create the string with all the criteria and values and store that in a variable.

Here is the example:

Filter_con has the following value

Criteria1:=">10", Operator:=xlAnd, Criteria2:="<100000000",Operator:=xlFilterValues

When I am trying to execute the code

Selection.AutoFilter Field:=235, Filter_con

I am getting the error:

Run time error: 1004 AutoFilter method of range class failed.

Here is the code

t_lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A3:XFD" & t_lastrow).Select
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or 
ActiveSheet.FilterMode Then
Selection.ShowAllData
End If
Filter_Con=">10","<100000000"
Filter_numric_data = Split(Replace(Filter_Con, Chr(34), ""), ",")
UBU = UBound(Filter_numric_data)
     Filter_Con = ""
        For i__ = 0 To UBU
           If i__ <> UBU Then
             MsgBox (Filter_numric_data(i__))
            Filter_Con = Filter_Con & " Criteria" & i__ + 1 & ":=" & 
            Filter_numric_data(i__) & ", Operator:=xlAnd,"
           Else
            Filter_Con = Filter_Con & " Criteria" & i__ + 1 & ":=" & 
            Filter_numric_data(i__)
        End If
        Next
       Range("A3:XFD" & t_lastrow).Select
       Selection.AutoFilter Field:=Filter_Field & "," & Filter_Con

You can use the edit link under the question to change it / add additional information. FWIW it's not clear to me what your last comment means in the context of the information in the question... — Cindy Meister
– Cindy Meister, Commented Jan 28, 2019 at 6:49
If you want to reply to someone in comments you need to precede their name with @ - this will put a message in their inbox. For example: @satyanarayanavanka. Otherwise, comments are directed only at your inbox, as the person who asked the question. — Cindy Meister
– Cindy Meister, Commented Jan 28, 2019 at 9:00

user10981546 · Accepted Answer · 2019-01-28 22:19:37Z

Create a dictionary of all values to be shown. Use the dictionary items as the criteria1 with xlfiltervalues.

Option Explicit

Sub sdfgh()
    Dim vals As Variant, i As Long, dict As Object

    Set dict = CreateObject("scripting.dictionary")

    With Worksheets("sheet1")

        If .AutoFilterMode Then .AutoFilterMode = False

        vals = .Range(.Cells(2, "IA"), .Cells(.Rows.Count, "IA").End(xlUp)).Value2

        For i = LBound(vals, 1) To UBound(vals, 1)

            Select Case True
                Case vals(i, 1) > 100 And vals(i, 1) < 1000 And vals(i, 1) <> 500
                    'xlFilterValues requires text
                    dict.Item(vals(i, 1)) = CStr(vals(i, 1))
                Case Else
                    'do nothing
            End Select

        Next i

        With .Cells(1, "A").CurrentRegion

            .AutoFilter field:=235, Criteria1:=dict.items, Operator:=xlFilterValues

        End With

    End With
End Sub

Collectives™ on Stack Overflow

Excel VBA Dynamic filters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related