How to delete multiple rows/columns in Excel.(Power Automate Desktop)

03/05/202303/18/2023

Japanese version.

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.

---