How to: Enable Filtering
- 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.
- Use the Worksheet.AutoFilter property to get access to the SheetAutoFilter object. This object inherits the AutoFilterBase interface, which provides basic methods and properties used to apply, clear or disable a filter and sort values in the filtered range.
- Call the SheetAutoFilter.Apply method to enable filtering. Pass the CellRange object you wish to filter as a parameter.
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);
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.
// Access a table. Table table = worksheet.Tables; // Filter values in the "Amount" column that are greater than 75$. table.AutoFilter.Columns.ApplyCustomFilter(75, FilterComparisonOperator.GreaterThanOrEqual);
The image below illustrates the result of executing the code (the workbook is opened in Microsoft® Excel®).
If you disabled the filtering functionality for a particular table, you can reactivate it by using the TableAutoFilter.Apply method.