Excel VBA Autofilter Using UserForm

Excel VBA Autofilter Using The UserForm

         VBA AutoFilter method is a useful, fast way to perform database and spreadsheets filtering in Excel. 

Method simply hides the rows that don’t match user criteria so user can ignore the unrelated data. 

VBA AutoFilter method allows user filter multiple fields at once and filter on more than one criteria.

 In our template ,we performed the filtering process (VBA Autofilter Method) in the worksheet using the userform. The data in columns B, C and D are filtered according to the selected value from the drop-down lists on the userform. The text color is changed to the blue color to highlight the filtered data.

We used the following codes for data filtering in the columns using the AutoFilter method :

Range("A2:G" & Cells(Rows.Count, 7).End(xlUp).Row).AutoFilter Field:=2, Criteria1:=ComboBox2.Value
Range("A2:G" & Cells(Rows.Count, 7).End(xlUp).Row).AutoFilter Field:=3, Criteria1:=ComboBox3.Value
Range("A2:G" & Cells(Rows.Count, 7).End(xlUp).Row).AutoFilter Field:=4, Criteria1:=ComboBox4.Value

If we examine the codes above; we chose starting cell A2 and the last used cell range in column G as the range :

Range("A2:G" & Cells(Rows.Count, 7).End(xlUp).Row)
Field expression indicates the column to be filtered.
The specifier Field:=2 points to column B, Field:=3 to column C, and Field:=4 to column D.

We filled the data in columns B, C, and D into drop-down lists, uniquely and sequentially. For example ; we used the following codes to populate the data in column B as unique and the sorted ascending values into the drop-down list (ComboBox2) :

...
Dim i, a, b As Long, c As Variant
For i = 3 To Cells(Rows.Count, 2).End(xlUp).Row
If WorksheetFunction.CountIf(Range("B3:B" & i), Range("B" & i)) = 1 Then
ComboBox2.AddItem Range("B" & i)
End If
For a = 0 To ComboBox2.ListCount - 1
  For b = a To ComboBox2.ListCount - 1
        If ComboBox2.List(b) < ComboBox2.List(a) Then
c = ComboBox2.List(a)
    ComboBox2.List(a) = ComboBox2.List(b)
    ComboBox2.List(b) = c
       End If
  Next
  Next
  Next
...

The captions of the option buttons are automatically taken from the worksheet when userform is opened.

Private Sub UserForm_Initialize()
...
Sheets("Sheet1").Activate

OptionButton2.Caption = Range("B2").Value
OptionButton3.Caption = Range("C2").Value
OptionButton4.Caption = Range("D2").Value
...
End Sub