Get empty cell in Excel worksheet Action(Power Automate Desktop)
This action retrieves the position of an empty cell from a specified cell range.
This action was added in the March 2024 update.
How to use.
First, set up Launch Excel Action or Attach to running Excel and specify the target Excel file, and then.
Drag "Get empty cell" from "Excel" under "Actions".
If you want to save your edits, you need to open the file in a non-read-only setting.
Set parameters.
The edited contents will not be reflected in the file as is.
If you wish to keep the edited content, you must save it using Save Excel Action or Close Excel Action at the end.
Parameters
Excel instance
Specify the Excel file.
Specify the Variables produced for the Launch Excel Action or the Attach to running Excel.
Operation
Specifies which cells are to be retrieved by the generated variable if there are multiple empty cells in the specified cell range.
- First empty cell
- First empty cell in column
- First empty cell in row
- All empty cells
First empty cell
Get the position of the first cell in the entire cell range.
Additional parameter specifies "Search direction".
- By row
- By column
For example, a cell range from A1 to E5 (green) with values in A1 to C3 would result in the following
| Search direction | Result |
|---|---|
| By row | D1(Red) |
| By column | A4(Yellow) |
First empty cell in column
Specify targets by sequential number with the first column as 1.
First empty cell in row
Specify targets by sequential number with the first row as 1.
All empty cells
The generated variables become Data table.
There are no additional parameters to specify.
Search in
Specifies a range of cells to search.
- Named cells
- Specific range
Let's go ahead and set up the "Set active Excel worksheet Action" first, since it does not specify the sheet, but the active sheet.
Named cells
Specify the name of the cell range in the "Cells Name" field.
Specific range
Specify the sorting target by Start column/Start row/End column/End row.
The first row and column are sequential numbers of 1.
Variables produced
The position of the empty cell found. Sequential numbers with the first row and column as 1.
EmptyCellColumnIndex,EmptyCellRowIndex
If the "Operation" is any of the following, it will be this variable
- First empty cell
- First empty cell in column
- First empty cell in row
EmptyCells
If "Operation" is "All empty cells", this variable will be.
EmptyCells is a Data table. To use individual values, the formula is as follows
Get a single value
"#" is a sequential number with leading zeros.
%EmptyCells[#]['ColumnIndex']%
%EmptyCells[#]['RowIndex']%
Get all values (loop)
Use "For each".
Specify "EmptyCells" for "Value to iterate".
The following expression is specified in the loop.
%CurrentItem['ColumnIndex']%
%CurrentItem['RowIndex']%
For those who want to learn Power Automate Desktop effectively
The information on this site is now available in an easy-to-read e-book format.
Or Kindle Unlimited (unlimited reading).
You willl discover how to about basic operations.
By the end of this book, you will be equipped with the knowledge you need to use Power Automate Desktop to streamline your workflow.