Strange behavior in Excel while deleting row using PAD

Like (0) Share

Report

Posted on 1 Jun 2024 03:02:35 by tlraghavendra

I am using excel rows crosses 50-80K

Task is to do some to variable filter and delete those rows.

Initially I created flow as follows

Created column called RowIndex and given formula "=Row(A2)"
Selected all cell of RowIndex
Send key: Control+D
Filter cells: using standard action from PAD to column which I needed
Copy All available cells
added new sheet : TempDel
Paste to A1
read and added to DataTable : ToDelDT
Activate back to data sheet
for each row of ToDel
- delete row of RowIndex+count of deleted and increased count

This way works, but lots of time consuming.

In order to shorten process, I try Delete row shortcut key (Control+(-) minus), it works fine, takes about 3-5 seconds, if I open excel manually.

Filter cells: using standard action from PAD to column which I needed
select filter column first row like "A1"
Send Key: {Down}
Send key: {Control}({Shift}({Down})) for
Send key: {Control}(-)

The moment Delete row shortcut key send, Excel app become very busy for at min 5 mins to respond back.

it is same case if I use another shortcut key "{Alt}(){H}{D}{R}".

I am not sure what is the reason behind "not responding" for longer period. Please advise.

Regards,

Raghavendra

I have the same question (0)

All responses (2)

Answers (0)

tlraghavendra 22 on 02 Jun 2024 at 14:57:58

Like (0)

Report

Re: Strange behavior in Excel while deleting row using PAD

Hi @eetuRobo,

Thank you for reply,

Excel and PAD both are latest.

I did try your VB script, still same Excel is not responding. Yes, smaller number of rows yours as well as my method both works with out any issues. I did follow my first method to delete row.

Thanks once again.

Regards,
Raghavendra.

Was this reply helpful? Yes No
eetuRobo 3,874 Super User 2025 Season 2 on 01 Jun 2024 at 18:37:04

Like (1)

Report
Re: Strange behavior in Excel while deleting row using PAD

I would check the following:
-Is your Excel up to date. In Excel go to File > Account and check for updates.
-Is your Power Automate Desktop up to date
-Check if there is some spike in CPU or RAM when running the flow.

For troubleshooting I would test with an Excel work book which has only 10 rows. Does it work normally then or still crash? If it works maybe your machine is having trouble when doing such a task on such a big excel file with PAD flow. So it could be about your machines hardware not being powerful enough. Or maybe a clean windows install would make the machine fast enough to do the task
Maybe your machine is just powerful enough do it when you do it manually but when there is automation flow running on top of that it doesn't have enough processing power.

I tried with your shortcut key method ({Alt}(){H}{D}{R}) in a flow with 50k rows(size of the excel 6,808 KB) and my Excel doesn't crash like yours.

Try doing the task from VBscript and see if that works.
Like this one:

You need to have the Excel open before you run this script.

Copy this code to your PAD to add the VBScript action:
@@copilotGeneratedAction: 'False' Scripting.RunVBScript.RunVBScript VBScriptCode: $'''\' Get the running instance of Excel Set objExcel = GetObject(, \"Excel.Application\") Set objShell = CreateObject(\"WScript.Shell\") \' Check if Excel is running If objExcel Is Nothing Then WScript.Echo \"Excel is not running.\" WScript.Quit End If \' Open the Excel workbook Set objWorkbook = objExcel.ActiveWorkbook \' Access the first worksheet Set objWorksheet = objWorkbook.Worksheets(1) \' Focus on the Excel window objShell.AppActivate objExcel.Application.Caption \' Select the entire Column A objWorksheet.Columns(\"A:A\").Select \' Use SendKeys to simulate pressing Ctrl + Minus objShell.SendKeys \"^-\" \' Save the workbook \'objWorkbook.Save \' Close the workbook \'objWorkbook.Close \' Quit the Excel application \'objExcel.Quit \' Release the objects Set objShell = Nothing Set objWorksheet = Nothing Set objWorkbook = Nothing Set objExcel = Nothing WScript.Echo \"Rows with values in Column A have been deleted.\" ''' ScriptOutput=> VBScriptOutput ScriptError=> ScriptError

Was this reply helpful? Yes No