Power Automate

Delete all rows downwards once a value is found

(1) Share

Report

Posted on by MB-26070843-0

Hi,

I have a report sent weekly that shows low stock inventory once the items gets under a certain value.

It is in alpha order so I have a flow that sorts in descending order by stock level.

I need it so all rows that show '0' are removed. (the software we use sees 0 stock as being low stock..)

Created a loop with a row counter that then deletes the row in the excel file but it has 12k lines, and was estimating to run for 6hrs! I thought about selecting rows downwards from active cell and then send keys to delete but as it is in a for each loop within a data table and not in the workbook I cannot get it to work.

Any help would be really appreciated!!!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Michael E. Gernaey 53,433 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

If I were you, I would create a Script IN Excel that selects the Range and Deletes them.

Call this Script from Power Automate.

Don't loop in Power Automate.

Was this reply helpful? Yes No
Suggested answer

WillSG 352 Moderator on at

Like (0)

Report
Hi @MB-26070843-0 I hope you are doing well.

You can utilize the action named Run VBScript and run a small script to delete all the rows that have Zeros.

Here is the snippet code tested to delete any row on Column A that contains Zeros of an already opened file. This code goes into the Run VBScript Action showed below.

' Create Excel application object Set objExcel = GetObject(, "Excel.Application") ' Directly reference the workbook and worksheet Set objWorkbook = objExcel.Workbooks("Book1.xlsx") Set objWorksheet = objWorkbook.Sheets("Sheet1") ' Determine the last row with data in column A Dim lastRow, i lastRow = objWorksheet.Cells(objWorksheet.Rows.Count, 1).End(-4162).Row ' -4162 is xlUp ' Loop through each row from the last to the first For i = lastRow To 1 Step -1 If objWorksheet.Cells(i, 1).Value = 0 Then objWorksheet.Rows(i).Delete End If Next ' Save the workbook objWorkbook.Save

Please let me know if it works, happy automating!

If I have addressed your inquiry successfully, kindly consider marking my response as the preferred solution. If you found my assistance helpful, a 'Thumbs Up' would be greatly appreciated.

Additionally, I offer specialized consultancy and development services leveraging PAD. If you're interested in exploring these services further, feel free to DM me, and we can initiate a discussion.

Finally, here is my YOUTUBE CHANNEL please subscribe to my channel and connect with me on LinkedIn Profile.

Kind regards,

Will SG
Founder & Director
RAMSolutions
www.ramscr.com

Was this reply helpful? Yes No
Suggested answer

eetuRobo 4,204 Super User 2025 Season 2 on at

Like (0)

Report

There are already good answers but I'll just throw my suggestion here too.

You could use Run .NET script -action with C# that copies the table up to that row number. Good thing about this is that there are no loops and it takes only couple of seconds to run the script.

References to be loaded -folder has to have System.Data.DataSetExtensions.dll and System.Xml.dll.
You should be able to find those files in C:\Windows\Microsoft.NET\Framework64\v4.0.30319 (or which ever version you have).

The C# code:
dt1 = dt1.AsEnumerable()
.Take(RowsToTake)
.CopyToDataTable();

Complete example flow:

ExcelData before Run .NET script

After Run .NET script

Then if you need to have it in Excel (either a new worksheet or clear it up first) then you can just use Write to Excel worksheet and use the %ExcelData%

I also tested with 12k rows to 4162 rows it only took 3 seconds to run the script.

to

Was this reply helpful? Yes No