VBA autofilter

In VBA, autofilter is useful to search specific information you want from a data range.

Worksheets("Sheet1").Range("A4:D30").AutoFilter field:=1, Criteria1:="JASON"

This is the autofilter. In the range from A4 cell to D30 cell, this arranges the data. But you can decide how it arranges the data.

The property Field 1 means the field of searching. If you write 1 for the Field property, then this searches the left-most line. If you write 2 for the Field property, this searches the second leftmost line. Criteria1 is the condition of search. This shows all data of JASON of the search range.

If you want to copy and paste the result of the filtering, write codes as follows:

lastRow = Worksheets("Sheet1").Range("A4").End(xlDown).Row
Worksheets("Sheet1").Range("A5:D" & lastRow).Copy
Worksheets("Sheet4").Range("A7").PasteSpecial

If you want to finish the filtering, write the autofilter method again. You don't need to write the properties for the method again.

Worksheets("Sheet1").Range("A4:D30").AutoFilter

Then the filtering stops working.

You can check if the sheet is in filter mode as following:

    If Worksheets("Sheet1").FilterMode = True Then
        Worksheets("Sheet1").Range("A4:D30").AutoFilter
    End If

If the sheet is in filter mode, this switches it off.

The code below is a sample code. If the value of combo-box is "option1", this searches for "1000" in range of A4:D30 in worksheet1. Then the result is copied to worksheet4.

Private Sub ComboBox1_Change()
If ComboBox1.Value = "Option1" Then
Worksheets("Sheet4").Range("A7:D30").Clear

    If Worksheets("Sheet1").FilterMode = True Then
        Worksheets("Sheet1").Range("A4:D30").AutoFilter
    End If

Worksheets("Sheet1").Range("A4:D30").AutoFilter Field:=1, Criteria1:="1000"
lastRow = Worksheets("Sheet1").Range("A4").End(xlDown).Row
Worksheets("Sheet1").Range("A5:D" & lastRow).Copy
Worksheets("Sheet4").Range("A7").PasteSpecial
End Sub