The requested page is not available for the requested platform. You are viewing the content for .NET Framework 4.5.2+ platform.

How to: Enable Filtering

  • 2 min 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 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.
  2. Call the SheetAutoFilter.Apply method to enable filtering. Pass the CellRange object you wish to filter as a parameter.
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.

SpreadsheetDocServer_EnabledAutoFilter

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[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®).

SpreadsheetDocServer_TableAutoFilter

Tip

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