code to filter values using multiple criteria

Salman Khan

I am making a small program. On main sheet, there are two combo boxes.

What I am trying to do, if I select value from each combo box, it will filter the data. But I am facing a small problem. I want a ALL value in both combo boxes and upon selecting that value it shouldn't filter that column.

So far my code is like this:

Sub submit()

    Dim ws As Worksheet, tbl As ListObject, rng As Range

    Set ws = Sheets("Graphical Summary")
    Set tbl = ws.ListObjects("Table5")
    Set rng = tbl.DataBodyRange

    With tbl
        .Range.AutoFilter Field:=1
        .Range.AutoFilter Field:=3
    End With

        With rng
            If Sheets("Graphical Summary").ComboBox1.Value = "All"     Then .AutoFilter Field:=2, Criteria1:=Sheets("Graphical Summary").ComboBox2.Value
            If Sheets("Graphical Summary").ComboBox1.Value <> vbNullString Then .AutoFilter Field:=1, Criteria1:=Sheets("Graphical Summary").ComboBox1.Value
            If Sheets("Graphical Summary").ComboBox2.Value <> vbNullString Then .AutoFilter Field:=2, Criteria1:=Sheets("Graphical Summary").ComboBox2.Value
        End With
End Sub

user4039065

Currently, you do not set a filter on field 1 if ComboBox1 is a zero-length string; extend that condition to include ALL.

With rng
    If ws.ComboBox1.Value <> vbNullString And
       ws.ComboBox1.Value <> "All" Then _
        .AutoFilter Field:=1, Criteria1:=ws.ComboBox1.Value
    If ws.ComboBox2.Value <> vbNullString Then _
        .AutoFilter Field:=2, Criteria1:=ws.ComboBox2.Value
End With

You've declared ws and assigned it to Sheets("Graphical Summary"); you might as well use it.

Collected from the Internet

Please contact [email protected] to delete if infringement.