- By default, Power Automate Desktop is only able to delete one Excel row at a time.
- But what if you would like to delete multiple rows at the same time?
- To add more complexity to your use case, what if you want to delete multiple non-consecutive rows
Eg; 1, 3:5, 7:8, 10
all at the same time?
Below is a Subflow that does exactly the same.
- No loops used to iterate through the Excel rows
- Developer can pass just the rows s/he wants to delete
- Deletes all specified rows in a single shot and not one by one
- Step by step explanation along with the Desktop flow attached below
This subflow need not be just used for deleting empty rows but to delete any other unwanted rows.
i) Assuming the input Excel to be as follows:
ii) And the rows you would like to delete are:
Row No. 3
Rows 7 to 8
Row No. 15
Row 18 to 19
iii) Follow the numbering in the screenshot
1: Set the full path of the Excel file
2: Set the rows you would like to delete
Instructions to pass rows are:
- Pass the multiple rows you want to delete separated by commas Eg; 1,3
- To use consecutive rows use a colon in between Eg; 2:5
- To use multiple consecutive rows, use a colon in between and separate them by a comma Eg; 1,3:5
Some different examples are:
1) 1,3:4,6 (will delete 1st, 3rd to 4th AND 6th row)
2) 2,5 (will delete ONLY 2nd AND 5th row)
3) 3:3,5:5 (will delete ONLY 3rd AND 5th row)
4) 3:5 (will delete ALL rows from 3rd to 5th row)
5) 1,3:5,7:8,10 (will delete 1st, 3rd to 5th, 7th to 8th and 10th row)
- You can also pass it as variables Eg; %var1%,%var2%
Notice the comma in between the two variables
- Another example of variables is %var1%:%var2%
Notice the colon between the variables
3: Pass the name of the Worksheet from which you want to delete the rows
You can add your own validation to check if the sheet exists.
iv) You don't need to do anything in the below steps. They are just for explanation.
4: The sheet that you want the rows to be deleted will be activated if its not already.
This is an optional action. You can delete it if not required.
5: This step will call a Subflow which has a vbscript written to delete the passed rows.
You will be able to see the code in the vbscript when you download the desktop flow attached to this page.
6: Saves the Excel before closing.
The good part is you can perform any of the other Excel operations before or after deleting the rows without closing and re-opening the file.
v) Initial input and final output after running the Flow
This is just an example on how to use the Subflow and the structure of the Excel may not be as below.
As a developer you need to find out the rows to delete by using any of the actions available in Power Automate Desktop.
Or in some cases the developer would already know which rows to delete beforehand based on the structure of the input file.
- Instructions on how to copy the attached Desktop Flow and Subflow
1) There are two text files as follows
2) Copy-Paste the content of the Main.txt into the Main Flow editor
3) Create a new Subflow with the name Delete_Excel_Rows and Copy-Paste the content of Delete_Excel_Rows.txt into the editor of the newly created subflow.
Sample of how to do a copy paste is as below
4) The structure of the Main flow and the Subflow looks as below
5) Refer the comments in the Flow, make changes to parameters and Run the Main flow.
Make sure to run it against a copy of your Excel at first and not an original Excel file.
