Edit

Share via

ExcelScript.AutoFilter interface

  • Reference
Package:
ExcelScript

Represents the AutoFilter object. AutoFilter turns the values in Excel column into specific filters based on the cell contents.

Remarks

Examples

/**
 * This script creates an autoFilter on the worksheet that filters out rows based on column values. 
 * The autoFilter filters to only include rows that have a value in column C in the lowest 10 values 
 * (of column C values).
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the autoFilter of the first table in the current worksheet.
  const currentSheet = workbook.getActiveWorksheet();
  const dataRange = currentSheet.getUsedRange();
  const autoFilter = currentSheet.getAutoFilter();

  // Add a filter that will only show the rows with the lowest 10 values in column C
  // (index 2, assuming the used range spans from at least A:C).
  autoFilter.apply(dataRange, 2, {
    criterion1: "10",
    filterOn: ExcelScript.FilterOn.bottomItems
  });
}

Methods

apply(range, columnIndex, criteria)

Applies the AutoFilter to a range. This filters the column if column index and filter criteria are specified.
clearColumnCriteria(columnIndex)

Clears the column filter criteria of the AutoFilter.
clearCriteria()

Clears the filter criteria and sort state of the AutoFilter.
getCriteria()

An array that holds all the filter criteria in the autofiltered range.
getEnabled()

Specifies if the AutoFilter is enabled.
getIsDataFiltered()

Specifies if the AutoFilter has filter criteria.
getRange()

Returns the Range object that represents the range to which the AutoFilter applies. If there is no Range object associated with the AutoFilter, then this method returns undefined.
reapply()

Applies the specified AutoFilter object currently on the range.
remove()

Removes the AutoFilter for the range.

Method Details

apply(range, columnIndex, criteria)

Applies the AutoFilter to a range. This filters the column if column index and filter criteria are specified.

apply(
            range: Range | string,
            columnIndex?: number,
            criteria?: FilterCriteria
        ): void;

Parameters

range

ExcelScript.Range | string

The range on which the AutoFilter will apply.

columnIndex

number

The zero-based column index to which the AutoFilter is applied.

criteria
ExcelScript.FilterCriteria

The filter criteria.

Returns

void

Examples

/**
 * This script applies a filter to a table so that 
 * only rows with values in column 1 that start with "L" are shown.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the autoFilter of the first table in the current worksheet.
  const table = workbook.getActiveWorksheet().getTables()[0];
  const autoFilter = table.getAutoFilter();

  // Filter to only include values that start with "L".
  const filterCriteria: ExcelScript.FilterCriteria = {
    filterOn: ExcelScript.FilterOn.custom,
    criterion1: "L*"
  };
  
  // Apply the filter to column 1 (zero-based).
  autoFilter.apply(table.getRange(), 1, filterCriteria);
}

clearColumnCriteria(columnIndex)

Clears the column filter criteria of the AutoFilter.

clearColumnCriteria(columnIndex: number): void;

Parameters

columnIndex

number

The zero-based column index, which represents which column filter needs to be cleared. If the index value is not supported (for example, if the value is a negative number, or if the value is greater than the number of available columns in the range), then an InvalidArgument error will be thrown.

Returns

void

clearCriteria()

Clears the filter criteria and sort state of the AutoFilter.

clearCriteria(): void;

Returns

void

Examples

/**
 * This script clears any applied criteria from the worksheet's autoFilter.
 */
function main(workbook: ExcelScript.Workbook) {
  const currentSheet = workbook.getActiveWorksheet();

  // Clear all the criteria currently applied to the autoFilter.
  currentSheet.getAutoFilter().clearCriteria();
}

getCriteria()

An array that holds all the filter criteria in the autofiltered range.

getCriteria(): FilterCriteria[];

Returns

ExcelScript.FilterCriteria[]

getEnabled()

Specifies if the AutoFilter is enabled.

getEnabled(): boolean;

Returns

boolean

getIsDataFiltered()

Specifies if the AutoFilter has filter criteria.

getIsDataFiltered(): boolean;

Returns

boolean

getRange()

Returns the Range object that represents the range to which the AutoFilter applies. If there is no Range object associated with the AutoFilter, then this method returns undefined.

getRange(): Range;

Returns

ExcelScript.Range

reapply()

Applies the specified AutoFilter object currently on the range.

reapply(): void;

Returns

void

remove()

Removes the AutoFilter for the range.

remove(): void;

Returns

void