How to autofilter continuously for two fields with two different criterias in vba?

Question

Search 1

enter image description here

Continue with Search 2

enter image description here

And a Search button to perform the autofilter action

Private Sub CommandButton1_Click() 'Search button
    Dim rData  As Range

    With Sheet1

        Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 8).End(xlUp))

        If Not .AutoFilterMode Then .Cells(1, 1).AutoFilter
        .Cells(1, 1).AutoFilter Field:=lFld, Criteria1:=sCrit            
        'Header
        On Error Resume Next
        Set rSource = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)

        ActiveSheet.AutoFilterMode = False

        On Error GoTo 0
        .Cells(1, 200).CurrentRegion.ClearContents
        rSource.Copy .Cells(1, 200)

        Set rSource = .Cells(2, 200).CurrentRegion
        Set rSource = rSource.Offset(1, 0).Resize(rSource.Rows.Count - 1, _
                                                  rSource.Columns.Count)
    End With

    With Me.ListBox1
        .RowSource = ""
        .RowSource = rSource.Address(external:=True)
    End With

End Sub

They work perfectly if I autofilter them at a field and a criteria one at a time. Now my question is how can I autofilter the optionbutton and then continue and filter the data for combobox?

Edited: my listbox does not seem to update accordingly with the excel after search 2. How can I amend my code such that it will refresh from the first search and list out the updated search 2 result? Please advise.

Make sure that Sheet1 is updated correctly. If so, add `DoEvents` before updating the Listbox1 row source. I have already pointed this out in this [post](http://stackoverflow.com/questions/23824749/how-to-update-multiple-selected-rows-with-a-same-value-under-a-same-column-in-li/23830161#23830161). Also better if you'll have your filtered data and the listbox source separately. — L42, May 30 '14 at 05:34
So DoEvents Listbox1.RowSource = "'[WorkbookName]SheetName'!RangeAddress" — Et Andrea, May 30 '14 at 05:54
Yeah. Take note of a dummy or temporary sheet for the listbox display. If you'll have your data in the same sheet, even if you put it in the far end column, when you autofilter, it will still be affected. To not worry the Listbox display, have a separate sheet intended for the display only. — L42, May 30 '14 at 05:58

score 0 · Answer 1 · answered May 30 '14 at 03:43

If you don't turn off the existing filters (such as by calling ActiveSheet.AutoFilterMode = False), then setting the AutoFilter (for a different field) will add to any existing filter rather than replacing it. For example:

ActiveSheet.Range("$A$1:$D$22").AutoFilter Field:=4, Criteria1:="1"
ActiveSheet.Range("$A$1:$D$22").AutoFilter Field:=1, Criteria1:="DEF"

will only show results where Column 1 contains DEF AND column 4 contains 1.

However, if you set AutoFilter again for the same field, you will replace the setting, for example:

ActiveSheet.Range("$A$1:$D$22").AutoFilter Field:=1, Criteria1:="ABC"
ActiveSheet.Range("$A$1:$D$22").AutoFilter Field:=1, Criteria1:="DEF"

will only show results where Column 1 contains DEF.

Ok. I get it. So I turned my autofiltermode to true now. And it filters accordingly. But how can I refresh the auto filter because the first result will stay in the listbox. — Et Andrea, May 30 '14 at 04:19

score 0 · Accepted Answer · edited May 23 '17 at 11:50

As commented, something like this might work:

With Sheet1
    Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 8).End(xlUp))
    .AutoFilterMode  = False
    '~~> you are filtering rData right? so work on it directly.
    rData.AutoFilter lFld, sCrit 

    'Header
    On Error Resume Next
    Set rSource = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    .AutoFilterMode = False
    On Error GoTo 0

    '~~> Use Sheet2 here or a temporary sheet for listbox display purpose only
    Sheet2.Cells.ClearContents
    rSource.Copy Sheet2.Cells(1, 1)

    Set rSource = Sheet2.Range(Sheet2.Cells(1, 1) _
                    , Sheet2.Cells(Sheet2.Rows.Count, 8).End(xlUp))
    Set rSource = rSource.Offset(1, 0).Resize(rSource.Rows.Count - 1, _
                                              rSource.Columns.Count)
End With

DoEvents '~~> Again this is a must to visually update ListBox display
Me.ListBox1.RowSource = rSource.Address(external:=True)

I changed some parts of your code to make it work on the actual objects.
Avoid using Activesheet as discussed in the link.
Also, I'm comfortable in defining the actual range rather that using CurrentRegion or UsedRange.

How to autofilter continuously for two fields with two different criterias in vba?

2 Answers2