How to delete multiple rows/columns in Excel.(Power Automate Desktop)
Introduces how to delete multiple rows or multiple columns in an Excel book.
There are two ways of deleting: Delete one line at a time and Delete all at once.
Deleting in batches is faster, but it involves keystrokes, so it may fail due to the user's mouse or keystrokes.
Deleting one line at a time takes longer, but it is stable because there is no key operation. Also, the flow is easy to understand.
If the execution time is not a problem, delete one line at a time.
If it is too slow, it is better to delete them all at once.
Overall view of Flow
Delete one row at a time
Delete one column at a time
Delete all rows at once
Delete all columns at once
Robin(for copy and paste)
Delete one row at a time
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Test\\Test.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet1'''
SET Start TO 3
LOOP LoopIndex FROM Start TO 6 STEP 1
Excel.DeleteRow Instance: ExcelInstance Index: Start
END
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
Delete one column at a time
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Test\\Test.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet1'''
SET Start TO 3
LOOP LoopIndex FROM Start TO 6 STEP 1
Excel.DeleteColumn Instance: ExcelInstance Column: Start
END
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
Delete all rows at once
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Test\\Test.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet1'''
Excel.SelectCellsFromExcel.SelectCells Instance: ExcelInstance StartColumn: 1 StartRow: 3 EndColumn: 16384 EndRow: 6
MouseAndKeyboard.SendKeys.FocusAndSendKeys TextToSend: $'''{LControlKey}({Subtract})''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
Delete all columns at once
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Test\\Test.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet1'''
Excel.SelectCellsFromExcel.SelectCells Instance: ExcelInstance StartColumn: 3 StartRow: 1 EndColumn: 6 EndRow: 1048576
MouseAndKeyboard.SendKeys.FocusAndSendKeys TextToSend: $'''{LControlKey}({Subtract})''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
Flow creation steps
Delete one row/column at a time
Set up a Launch Excel Action.
| Parameter | Value |
|---|---|
| Launch Excel | and open the following document |
| Document path | Path of the target Excel book. |
| Make instance visible | OFF |
| Open as ReadOnly | OFF |
Set up a Set active Excel Worksheet Action.
| Parameter | Value |
|---|---|
| Active worksheet with | Name |
| Worksheet name | The name of the target sheet. |
Create the variable %start% to specify the initial number of rows or columns to start deleting.
Set up a Loop.
| Parameter | Value |
|---|---|
| Start from | %start% |
| End to | Number of rows or columns to finish deleting. |
| Increment by | 1 |
Set up a Delete row from Excel worksheet or Delete column from Excel worksheet in the Loop.
| Parameter | Value |
|---|---|
| Delete row/column | %start% |
Finally, set up a Close Excel Action and save the file.
Delete all rows/columns at once
Set up a Launch Excel Action.
| Parameter | Value |
|---|---|
| Launch Excel | and open the following document |
| Document path | Path of the target Excel book. |
| Make instance visible | ON |
| Open as ReadOnly | OFF |
Set up a Set active Excel Worksheet Action.
| Parameter | Value |
|---|---|
| Active worksheet with | Name |
| Worksheet name | The name of the target sheet. |
Set up a Select Cells in Excel worksheet.
| Parameter | Value |
|---|---|
| Select | Range of cells |
| Start column | 1 |
| Start row | Start row to be deleted. |
| End column | 16384 |
| End row | Last row to be deleted. |
| Parameter | Value |
|---|---|
| Select | Range of cells |
| Start column | Start column to be deleted. |
| Start row | 1 |
| End column | Last column to be deleted. |
| End row | 1048576 |
Set up a Send Keys and specify the - (minus) key qualified with the CTRL key.
{LControlKey}({Subtract})
Finally, set up a Close Excel Action and save the file.
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.