How to: Enable Filtering

• May 10, 2024
• 2 minutes to read

The examples below demonstrate how to enable a filtering functionality in a workbook.

Filter a Worksheet Range

To activate filtering for a specific range in a worksheet, do the following.

1. Use the Worksheet.AutoFilter property to obtain the SheetAutoFilter object. This object inherits the AutoFilterBase interface, which contains members used to apply, clear or disable a filter and sort values in the filtered range.
2. Call the SheetAutoFilter.Apply method to enable filtering. Pass the CellRange object you wish to filter as a parameter.

View Example

• AutoFilterActions.cs
• AutoFilterActions.vb

Worksheet worksheet = workbook.Worksheets["Regional sales"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Enable filtering for the specified cell range.
CellRange range = worksheet["B2:E23"];
worksheet.AutoFilter.Apply(range);

Dim worksheet As Worksheet = workbook.Worksheets("Regional sales")
workbook.Worksheets.ActiveWorksheet = worksheet

' Enable filtering for the specified cell range.
Dim range As CellRange = worksheet("B2:E23")
worksheet.AutoFilter.Apply(range)

The image below illustrates the result (the workbook is opened in Microsoft® Excel®). Once filtering is activated, a drop-down arrow appears on the right side of each column header in the filtered range. A user can click the arrow of the desired column and select the required filter options in the AutoFilter drop-down menu.

Filter a Table

By default, when you create a table in a worksheet, it already has the AutoFilter functionality turned on. Thus, to filter data in the table columns, get access to the required table in the worksheet’s TableCollection, and then use the Table.AutoFilter property to return the TableAutoFilter object. This object inherits the AutoFilterBase base interface, which provides common methods and properties used to apply, clear or disable a table filter and sort values in the table columns in the same manner as it can be done for a worksheet range.

• C#
• VB.NET

// Access a table.
Table table = worksheet.Tables[0];
// Filter values in the "Amount" column that are greater than 75$.
table.AutoFilter.Columns[4].ApplyCustomFilter(75, FilterComparisonOperator.GreaterThanOrEqual);

' Access a table.
Dim table As Table = worksheet.Tables(0)
' Filter values in the "Amount" column that are greater than 75$.
table.AutoFilter.Columns(4).ApplyCustomFilter(75, FilterComparisonOperator.GreaterThanOrEqual)

The image below illustrates the result of executing the code (the workbook is opened in Microsoft® Excel®).

Tip

If you disabled the filtering functionality for a particular table, you can reactivate it by using the TableAutoFilter.Apply method.