How to: Clear a Filter

• Apr 01, 2021
• 2 minutes to read

The examples below demonstrate how to clear or disable a filter to redisplay all the rows in the filtered range.

• Clear a filter from a column

  To remove a filter from a specific column, access this column by its index in the AutoFilterColumnCollection collection, and then call the AutoFilterColumn.Clear method.

  C#

  // Enable filtering for the specified cell range.
  CellRange range = worksheet["B2:E23"];
  worksheet.AutoFilter.Apply(range);
  
  // Filter values in the "Sales" column that are greater than 5000$.
  worksheet.AutoFilter.Columns[2].ApplyCustomFilter(5000, FilterComparisonOperator.GreaterThan);
  
  // Remove the filter from the "Sales" column.
  worksheet.AutoFilter.Columns[2].Clear();
  

  VB.NET

  ' Enable filtering for the specified cell range.
  Dim range As CellRange = worksheet("B2:E23")
  worksheet.AutoFilter.Apply(range)
  
  ' Filter values in the "Sales" column that are greater than 5000$.
  worksheet.AutoFilter.Columns(2).ApplyCustomFilter(5000, FilterComparisonOperator.GreaterThan)
  
  ' Remove the filter from the "Sales" column.
  worksheet.AutoFilter.Columns(2).Clear()
  

• Clear all filters

  To clear all the filters specified in a worksheet, use the AutoFilterBase.Clear method of the SheetAutoFilter object.

  View Example

  AutoFilterActions.cs

  // Enable filtering for the specified cell range.
  CellRange range = worksheet["B2:E23"];
  worksheet.AutoFilter.Apply(range);
  
  // Filter values in the "Sales" column that are greater than 5000$.
  worksheet.AutoFilter.Columns[2].ApplyCustomFilter(5000, FilterComparisonOperator.GreaterThan);
  
  // Clear the filter.
  worksheet.AutoFilter.Clear();
  

  AutoFilterActions.vb

  ' Enable filtering for the specified cell range.
  Dim range As CellRange = worksheet("B2:E23")
  worksheet.AutoFilter.Apply(range)
  
  ' Filter values in the "Sales" column that are greater than 5000$.
  worksheet.AutoFilter.Columns(2).ApplyCustomFilter(5000, FilterComparisonOperator.GreaterThan)
  
  ' Clear the filter.
  worksheet.AutoFilter.Clear()
  

• Disable a filter

  To disable the filtering functionality, use the AutoFilterBase.Disable method of the SheetAutoFilter object. In this case, all the specified filters are removed, and the drop-down arrows disappear from the column headers in the filtered range.

  View Example

  AutoFilterActions.vb

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

  AutoFilterActions.cs

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