Find First/Last Data Row

UiPath.Excel.Activities.Business.FindFirstLastDataRowX

Finds the row number for the row where data starts and for the last row containing data in a specified sheet, range, or table. The activity can be used with an Excel file selected for a parent Use Excel File activity or with the Project Notebook.

Configuring the Activity

In the Body of the Activity

• Source - Click Plus on the right side of the field, then, from the menu, select the file and then a sheet, table, or range where to find the first and last rows with data, or select Indicate in Excel to indicate a range directly from the file. Alternatively, you can select Custom Input to enter the range manually, or Open in Advanced Editor to enter a VB expression.
• Has headers - If this option is selected, it indicates that the first row in the defined source range is a header row. When you then indicate the column to search for data from the Column name field, the values in the column headers are displayed under Range, enabling you to easily identify the columns. If this option is not selected, the options displayed under Range are the column letters when the source range is a sheet or a range, or the header row values when the source range is a table.
• Visible rows only - If this option is selected and the data in the source range is filtered, only values from the visible rows are returned. If not selected, the activity searches in the entire range.
• Column name - Click Plus on the right side of the field, then, from the menu, select Range, and then the column where to search for data. Depending on your selections for Source and Has headers, the columns are identified by the values in the first row of the selection or the letter of the column.

• Save first row number as - Click Plus on the right side of the field and then, from the menu, select where to save the number of the first row with data:

  • Select an Excel file and then a named cell, or select Indicate in Excel to indicate a cell from the file.
  • Copy to clipboard - Save the row number to the clipboard.
  • Save for Later Use - Save the row number for later use in the project.
  • Open in Advanced Editor - Enter a VB expression.

• Configure last row as - Select whether the last row should be returned as the last populated row or as the first row that is empty:

  • Last populated row - Returns the last populated row.
  • First empty row - Returns the first empty row.

• Save last row number as- Click Plus on the right side of the field and then, from the menu, select where to save the number of the last row with data:

  • Select an Excel file and then a named cell, or select Indicate in Excel to indicate a cell from the file.
  • Copy to clipboard - Save the row number to the clipboard.
  • Save for Later Use - Save the row number for later use in the project.

  • Open in Advanced Editor - Enter a VB expression.

    Note:

    • The activity returns a value of 0 for empty ranges when the last row is configured as First empty row. Otherwise, the returned value is -1.
    • If the Source property is set to a single cell, the activity returns all cells starting from the source cell to the last used cell in the sheet.

Properties Panel

Common

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

Input

• Blank rows to skip - Number of consecutive blank rows allowed in the data before determining the range has ended. The default value is 1.
• Column name - See Column name in the body of the activity.
• First row offset - Optionally, you can add a number of rows to the first row where data is found in the range. For example, if you enter 3, the activity finds the number of the third row with data. The default value is 0.
• Last row offset - Optionally, you can subtract a number of rows from the last row where data is found in the range. For example, if you enter 3, the activity finds the number of the third non-blank row from the bottom. The default value is 0.
• Source range - See Source in the body of the activity.

Misc

• Private - If selected, the values of variables and arguments are no longer logged at Verbose level.

Options

• Has headers - See Has headers in the body of the activity.

Output

• First row index - See Save first row number as in the body of the activity.
• Last row index - See Save last row number as in the body of the activity.