Find First or Last Data Row Workbook (preview)

Find First or Last Data Row Workbook activity that finds the first row with data and the last populated row in a column of a range.

UiPath.Excel.Activities.FindFirstLastDataRow

Description

Finds the index of the first row that contains data, and the index of the last populated row, or of the first empty row after it, in a column of a range. This is the cross-platform equivalent of the Find First/Last Data Row 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 search. If you don't specify a range, the whole used range of the sheet is used.
• Column name - The column to search for data. Enter its header name when Has headers is selected, or its letter otherwise. This field is required.
• First row offset - A number added to the index of the first row that contains data. For example, use it to get the row 2 rows below the first data row. The offset counts rows, not populated cells, so the result can be a blank row. When Visible rows only is selected, the offset counts visible rows. The default value is 0.
• Last row offset - A number subtracted from the index of the last row that contains data. For example, use it to get the row 3 rows above the last data row. The offset counts rows, not populated cells, so the result can be a blank row. When Visible rows only is selected, the offset counts visible rows. The default value is 0.
• Blank rows to skip - The number of consecutive blank rows allowed in the data before the activity decides that the data has ended. If you don't set this field, the value 1 is used.

Options

• Has headers - Select this option if the first row of the range is a header row. The data starts below it. By default, this option is selected.
• Configure last row as - Select how the last row is determined. This option doesn't affect the first row. You can choose from the following options:
  • Last populated row - The last row that contains data. This is the default value.
  • First empty row - The first empty row after the last populated row.
• Visible rows only - Select this option to use only the visible rows, taking into account the applied filters and ignoring the hidden rows. By default, this option is not selected.
• Password - The password of the workbook, if necessary.

Output

• First row index - The 1-based row number of the first row that contains data, stored as an Int32 variable. The value is -1 if the activity finds no data. This is the row number in the sheet, not a position relative to the range.
• Last row index - The 1-based row number of the last populated row, or of the first empty row, depending on Configure last row as. It is stored as an Int32 variable. The value is -1 if the activity finds no data, or 0 if Configure last row as is First empty row. This is the row number in the sheet, not a position relative to the range.

Use Workbook

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

Notes

• The activity only reads the workbook. It doesn't change it.
• Only the cells of the column in Column name are searched. A cell contains data when it is not empty and, for text, is not blank or only whitespace. Numbers, dates, booleans, and errors count as data. A formula counts through the value it calculates.
• The search stops at the first run of more than Blank rows to skip consecutive blank rows. With the default value of 1, a gap of two blank rows ends the data. The first row must come within that many blank rows from the start of the range, and the last row is the last populated row before such a gap.
• The offsets are applied last. The first row moves down by First row offset, and the last row moves up by Last row offset. If the result is outside the sheet, or the activity finds no data, the activity returns -1, or 0 for the last row when Configure last row as is First empty row.
• When Visible rows only is selected, hidden rows, for example the rows hidden by Filter Range Workbook, are ignored. They don't count as blank rows.
• If you don't specify a range, the activity searches the used range of the sheet, and Column name can be any column letter. A whole-column or whole-row range, such as A:A, is limited to the used range of the sheet, starting from row 1 for a column.
• Tables, named ranges, and pivot tables can't be used as a range reference. Pass the address of the table or named range as Range instead. A table includes its header row, so keep Has headers selected.
• Range must refer to the sheet in SheetName. A range that includes a sheet name, such as Sheet2!A1:A10, is not supported and causes an error. An unknown header, or a column letter outside the range, also causes an error.