Filter Range Workbook (preview)

Filter Range Workbook activity that filters a range by the values of one column, or clears the filters applied to a range.

UiPath.Excel.Activities.FilterRange

Description

Filters a range by the values of one column, or clears the filters applied to a range. You can filter by a list of allowed values (basic filter), or by one or two conditions (advanced filter). This is the cross-platform equivalent of the Filter Windows activity.

Note:

This activity does not support .xls files.

Project compatibility

Windows | Cross-platform

Properties

Common

• DisplayName - The name displayed for the activity in the Designer panel.

Input

• File - The full path of the resource workbook. To switch to a local path, select the plus icon, and then Use Local File. The current field changes to File (local path), where you can provide the full path of the workbook.
• SheetName - The name of the sheet from the workbook.
• Range - The range to filter. The first row of the range is the header row. If you don't specify a range, the whole used range of the sheet is used.
• Column Name - The header name of the column to filter on. This field is required unless Clear any existing filter is selected. When Clear any existing filter is selected, only the filter of this column is cleared. If you leave this field empty, every filter of the sheet is cleared.

Options

• Clear any existing filter - Select this option to clear the existing filters instead of applying a filter. By default, this option is not selected.
• Use advanced filter - Select this option to switch from the basic filter to the advanced filter. By default, this option is not selected.
• Basic filter - The values to keep. The activity keeps the rows whose value in Column Name equals any of the listed values.
• Advanced filter - One or two conditions, each made of an operator and a value. When you use two conditions, select And or Or to combine them.
• Password - The password of the workbook, if necessary.

Use Workbook

• Workbook - The existing workbook to use instead of a file path.

Advanced filter operators

The advanced filter supports the following operators:

• <, >, <=, >=, =, and !=
• Is empty and Is not empty. These operators don't take a value. Every other operator requires one.
• Starts with, Ends with, and Contains
• Does not start with, Does not end with, and Does not contain

The operators work as follows:

• Numbers and dates are compared as numbers and dates. Text is compared without taking the case into account.
• A text operator only matches text cells. Its negation also matches numbers and empty cells.
• In the value of the = and != operators and of the text operators, * stands for any characters, ? stands for one character, and ~ escapes a wildcard.

Notes

• The first row of the range is always the header row and is never hidden. Column Name is matched against the header names, without taking the case into account. It is never read as a column letter. A header name that doesn't match causes an error.
• The filter is saved in the workbook as an AutoFilter on the range, so Excel shows the filter buttons and the hidden rows. A sheet has a single AutoFilter:
  • Filtering another column of the same range keeps the existing filters, so the criteria of different columns are combined with AND.
  • Filtering a column again replaces its previous filter.
  • Filtering on a different range removes the existing filter and shows its rows again.
• A basic filter compares the values against the text displayed in the cell. For example, a date is compared as 1/5/2020, a number as 1,234.50, and an empty cell as an empty string.
• An advanced filter with >, >=, <, or <= on a number or a date compares the values of the cells, not the text that the number format displays. A column that displays 1 for both 1.2 and 1.4 is still split by "greater than 1.3". The other operators, including = and !=, are saved as the list of displayed values that match, as Excel does for a value filter. Two cells that display the same text are always kept or hidden together.
• The filter is saved as a list of the displayed values that match. Rows that you add later are not matched until you apply the filter again. Rows that were hidden manually are shown again when they match.
• Filters on several columns of the same range accumulate. A whole-column address, such as A:C, is resolved against the rows in use, so the filters stay together when rows are appended.
• Clearing a column that has no filter, or clearing when the sheet has no filter, does nothing. Clearing a column fails if the existing filter is on a different range.
• A whole-column or whole-row range, such as A:B, is limited to the used range of the sheet.
• Range must refer to the sheet in SheetName. A range that includes a sheet name, such as Sheet2!A:B, is not supported and causes an error. Range must not overlap an Excel table.