Unanswered

How do I delete a range of cells in Excel using Power Automate?

(0) Share

Report

Posted on by September

Hello,

How do I delete the values of a range of cells but keep the position of the adjacent cells? I tried it using the LOOP function but it did it one by one and it takes ages for it to clear a thousand cells, and it would be easier to just select the desired cells and delete them manually.

Below is an example of what the data would initially look like and how it should look like afterwards.

Before Before After After

Would appreciate any help 🙂

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (0)

VJR 7,635 on at

Like (1)

Report
Hi @September ,

Since there is no direct way to write a value to multiple cells you will have to do as below

- Copy cells from Worksheet

Copy any blank cell in your sheet eg; Z1

- Select cells from Worksheet

You can give a dynamic range here like B2 to B[total number of rows]

- Sendkeys Ctrl v

As if you are pasting a blank cell value to all those cells.

Another option is to use Delete Column and then Insert column and then write only the header text of the newly inserted column. In this case if you have formulas they might cause issues.

So you can go with either of the options accordingly.

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report

Select the range and then use SendKeys to send the {Delete} key. Delete is just clear contents, but no cells move.

Was this reply helpful? Yes No
kinuasa 795 Most Valuable Professional on at

Like (2)

Report
Hi,
I suggest that to use the 'Get first free row on column from Excel worksheet' action to get the blank cell in the specified column, then use the 'Select cells in Excel worksheet' action(End row parameter:%FirstFreeRowOnColumn - 1%) to select the cells to be deleted, and finally use the 'Run VBScript' action to execute the following script to delete the cells.

GetObject("%WorkbookPath%").Application.CommandBars.ExecuteMso "ClearContents"

Regards,
kinuasa

Sample Flow:
SET WorkbookPath TO $'''C:\\Test\\Excel\\SampleBook.xlsx''' Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: WorkbookPath Visible: True ReadOnly: False Instance=> ExcelInstance Excel.GetFirstFreeRowOnColumn Instance: ExcelInstance Column: $'''B''' FirstFreeRowOnColumn=> FirstFreeRowOnColumn Excel.SelectCellsFromExcel.SelectCells Instance: ExcelInstance StartColumn: $'''B''' StartRow: 2 EndColumn: $'''B''' EndRow: FirstFreeRowOnColumn - 1 Scripting.RunVBScript VBScriptCode: $'''GetObject(\"%WorkbookPath%\").Application.CommandBars.ExecuteMso \"ClearContents\"''' ScriptOutput=> VBScriptOutput

Was this reply helpful? Yes No