Filtering Data In Excel VBA

In Excel, you can analyze large datasets efficiently using filters, which let you specify which parts of the data should be visible. This filtering can be automated in VBA using the AutoFilter() method.

For demonstration, the example uses the Products table from the Northwind sample database. This table contains 78 records with six columns each. To illustrate a date filter, a seventh column with random date values is added using the following procedure:

Sub AddDateColumn()
    Dim i As Integer
    Randomize
    ThisWorkbook.Worksheets("Sheet5").Activate
    For i = 2 To 78
        Cells(i, 7).Value = Int(Rnd * 30) + 43900
    Next i
    Range("G2:G78").NumberFormatLocal = "DD.MM.YYYY"
End Sub

Explanation:

• The random number generator creates integer values between 43900 and 43929.
• When formatted as dates, these correspond to March 10, 2020, through April 8, 2020.
• The dates are placed in column 7 (column G).

Basic Number Filter

Sub NumberFilterEqualZero()
    ThisWorkbook.Worksheets("Sheet5").Activate
    ActiveSheet.UsedRange.AutoFilter
    ActiveSheet.UsedRange.AutoFilter 6, "0"
End Sub

• Calling AutoFilter() without parameters removes existing filters.
• The second call filters column 6 (stock quantity) to show only records where the value equals zero.
• The first parameter is the column number, and the second is the filter criterion.

Number Filter with Comparison Operator

Sub NumberFilterGreaterThan20()
    ThisWorkbook.Worksheets("Sheet5").Activate
    ActiveSheet.UsedRange.AutoFilter
    ActiveSheet.UsedRange.AutoFilter 5, ">20"
End Sub

• Displays only records where the value in column 5 (unit price) is greater than 20.
• Supported comparison operators: <, >, <=, >=, <>.
• The = operator acts like the equality example above.

Number Filter with Two Conditions on One Column

Sub NumberFilterBetween()
    ThisWorkbook.Worksheets("Sheet5").Activate
    ActiveSheet.UsedRange.AutoFilter
    ActiveSheet.UsedRange.AutoFilter 6, ">20", xlAnd, "<40"
End Sub

• Filters column 6 (stock quantity) to show only records where values are greater than 20 and less than 40.
• The constant xlAnd specifies the logical AND connection.

Two-Column Filter

Sub DoubleFilter()
    ThisWorkbook.Worksheets("Sheet5").Activate
    ActiveSheet.UsedRange.AutoFilter
    ActiveSheet.UsedRange.AutoFilter 5, ">20"
    ActiveSheet.UsedRange.AutoFilter 6, ">40"
End Sub

• Applies two filters sequentially:
  • Column 5 (unit price) greater than 20
  • Column 6 (stock quantity) greater than 40
• Both filters combine to restrict displayed records.

Text Filters

Single Text Filter

Sub TextFilterEqual()
    ThisWorkbook.Worksheets("Sheet5").Activate
    ActiveSheet.UsedRange.AutoFilter
    ActiveSheet.UsedRange.AutoFilter 3, "Beverages"
End Sub

• Shows only records where column 3 (category) equals « Beverages ».

Multiple Text Criteria (OR condition)

Sub TextFilterMultiple()
    ThisWorkbook.Worksheets("Sheet5").Activate
    ActiveSheet.UsedRange.AutoFilter
    ActiveSheet.UsedRange.AutoFilter 3, "Beverages", xlOr, "Spices"
End Sub

• Shows records where the category is either « Beverages » or « Spices ».
• The constant xlOr specifies the logical OR connection.

Text Filters with Wildcards

Starts With

Sub TextFilterStartsWith()
    ThisWorkbook.Worksheets("Sheet5").Activate
    ActiveSheet.UsedRange.AutoFilter
    ActiveSheet.UsedRange.AutoFilter 2, "M*"
End Sub

• Filters records where the value in column 2 (product name) starts with the letter « M ».

Contains Substring

Sub TextFilterContains()
    ThisWorkbook.Worksheets("Sheet5").Activate
    ActiveSheet.UsedRange.AutoFilter
    ActiveSheet.UsedRange.AutoFilter 2, "*ed*"
End Sub

• Shows records where the product name contains the substring « ed » anywhere.

Date Filter

Sub DateFilter()
    ThisWorkbook.Worksheets("Sheet5").Activate
    ActiveSheet.UsedRange.AutoFilter
    ActiveSheet.UsedRange.AutoFilter _
        7, ">=2020/3/29", xlAnd, "<=2020/4/3"
End Sub

• Shows records where the date in column 7 (last purchase date) is between March 29, 2020, and April 3, 2020, inclusive.
• Note: Use American date format (YYYY/MM/DD) for date criteria.