Delete rows of Excel with specific conditions.(Power Automate Desktop)

08/07/2023

Japanese version.

I will show you in the sample to delete a row if a particular column is blank.
The following worksheet has column A in rows 3 and 5 as blank and we want to delete this row.

Overall view of Flow

Robin(for copy and paste)

It can be copied and pasted into Power Automate Desktop.

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Test\\Test.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet1'''
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
LOOP LoopIndex FROM 1 TO FirstFreeRow STEP 1
    Excel.ReadFromExcel.ReadCell Instance: ExcelInstance StartColumn: 1 StartRow: LoopIndex ReadAsText: False CellValue=> ExcelData
    IF ExcelData = $'''%''%''' THEN
        Excel.DeleteRow Instance: ExcelInstance Index: LoopIndex
    END
END
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

Flow creation steps

Set up a Launch Excel Action.

ParameterValue
Launch Exceland open the following document
Document pathPath of the target Excel book.
Make instance visibleOFF
Open as ReadOnlyOFF

Set up a Set active Excel Worksheet Action.

ParameterValue
Active worksheet withName
Worksheet nameThe name of the target sheet.

Set up a Get first free column/row from Excel worksheet Action.

Set up a Loop.

ParameterValue
Start from1
End to%FirstFreeRow%
Increment by1

Set up a Read from Excel worksheet Action in Loop.

ParameterValue
RetrieveThe value of a single cell
Start column1
Start row%LoopIndex%

Set up a If in Loop.

ParameterValue
First operand%ExcelData%
OperatorEqual to (=)
Second operand%''%

Set up a Delete Row from Excel Worksheet Action in If.

ParameterValue
Delete row%LoopIndex%

Finally, set up Close Excel Action and save the file.

If column A is blank when this Flow is executed, the row is deleted.

Before Flow execution
After Flow execution

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.

Links

Tips(Power Automate Desktop)