VBA filtering

Filters are a very useful functionality that enables us to filter content in the table or area above which the autofilter was previously used based on a given criterion. This can be accomplished in many ways, but there is always an event that we are launching to perform the action. In this “recipe” you will learn how to filter the table with the help of previously prepared, combo lists.

To begin with, we created a simple combo list and created a procedure by which to fill its content and define the initial value:

Sub Init()

VBAProject.Sheet1.ComboBox1.AddItem “ALL”
VBAProject.Sheet1.ComboBox1.AddItem “Beograd”
VBAProject.Sheet1.ComboBox1.AddItem “Kragujevac”
VBAProject.Sheet1.ComboBox1.AddItem “Novi Sad”
VBAProject.Sheet1.ComboBox1.AddItem “Subotica”
VBAProject.Sheet1.ComboBox1.AddItem “Vršac”
VBAProject.Sheet1.ComboBox1.ListIndex = 0

End Sub

Then we write a code that will be activated after the ComboBox1 control value is changed. Filtering is achieved by first activating the worksheet and selecting the named range to be filtered. Then, for the given area and column, we perform a specification of the values based on which the filtering is done. By using the ShowAllData method, the filter is canceled.

Private Sub ComboBox1_Change()

Dim i As Integer
Dim value As String

i = VBAProject.Sheet1.ComboBox1.ListIndex
ActiveSheet.Cells(3, 1).Select

If i = 0 Then
    ActiveSheet.ShowAllData
Else
    Select Case i
        Case 1
        value = “Beograd”
        Case 2
        value = “Kragujevac”
        Case 3
        value = “Novi Sad”
        Case 4
        value = “Subotica”
        Case 5
        value = “Vršac”
    End Select
    With Sheet1
        .AutoFilterMode = False
        .Range(“Partneri”).AutoFilter
        .Range(“Partneri”).AutoFilter Field:=4, Criteria1:= value
    End With
End If

End Sub