Deleting filtered rows from large Excel table

I have a large Excel table that I need to do the following to:

  1. Filter one column.
  2. Delete the rows that remain after the filter has been applied.

Turns out it’s trickier than I thought when dealing with 1000+ rows that need to be deleted. Here’s what I’ve tried so far:

Attempt 1: Read excel table into datatable. Applied filter to datatable and saved output in new datatable. For each row in the filtered datatable, found index in Excel table and deleted it, one by one. This works but is extremely slow - it takes about 1 minutes to delete one row, and with 1000+ rows, that’s 16 hours, which is not an acceptable solution in my case.

Attempt 2: Used Filter Table activity on Excel table. Then used hotkeys to delete only visible rows. Works on smaller tables, but not when I attempt the bigger table (which is my real use case).

Attempt 3: I didn’t implement this attempt because I wasn’t able to even modify my Excel spreadsheet according to the prerequisite of the idea. In any case, this is the idea: Use structured formulas in Excel table (Excel crashed every time I tried to apply the structured formulas to the columns, which is why I wasn’t able to implement this). Read table into datatable. Filter data table. Delete rows in Excel table except for first row. Write datatable into Excel table, which should then keep the formulas…

Any other ideas for how to deal with this? The main problem seems to be that I need an efficient way to delete many rows from the Excel table…

Thanks for your ideas!

What is the size of the excel here ?

  1. Can we save the excel as .xlsb before processing, this will reduce the size.

To delete extra rows _ we can use ui automation.

  1. Open a blank excel via open application
  2. Using Click activity - Click File - Open - Browse
  3. Browse the path using typeinto and hit enter - this will open the excel.
  4. Now apply the filters same way we are doing manually.
  5. send hot key : ctrl shift end on the filtered data.
  6. Select Delete button
  7. Save the excel.


Mukesh

2 Likes

Hi @yvro

My guess is that you are using an Excel Scope to open the Workbook?

My suggestion is to use the Workbook read range activity to read the excel to a datatable. Once it is in the datatable, you can apply the filter on the datatable (using the filter datatable activity) and use a for each row on the filtered datatable with the delete row activity to delete the row in the big Excel Workbook.

This way, Excel should not crash - if it does still crash, perhaps you can have a static Excel template with the formulas applied on the columns. Once you’ve got the data you need, you can populate the Workbook.

Please shout if you get stuck and mark as solution if this helps :slight_smile:

Have a great one further!

1 Like

Hey @Jacqui_M - Thank you! Yes, that kind of sounds like what I was trying to do in attempt 3. I did get past Excel crashing (turned out the Excel table was spanning the entire 1M rows…), so I managed to read the data into a datatable and doing the filtering through the datatable filter wizard. But now when I do “Write Range” back to the Excel table, none of the formulas get applied to the added rows. I was thinking that because it’s an Excel table and the formulas are structured formulas, they should autopopulate when a new row is added, but it seems that’s not happening. Would you know how to fix that?

Hey @yvro yeah so my suggestion is to maybe delete the rows that don’t fit the criteria instead of using write range. In theory, that should work.

1 Like

Hi @yvro,

It may help to delete large data…

Regards
Balamurugan.S

2 Likes

I ended up deleting all the rows, then writing them back and adding the formula to the applicable columns… I might try writing a VBA script as well, seems like maybe that would be more efficient. Thanks for your help!

Ah, unfortunately I can’t really delete more than I should based on the filtering criteria! Otherwise that would have been a good idea.

Hey @yvro, maybe try deleting the rows you don’t need instead of all of the rows, that way you can maintain the existing calculations

Hi can any one help me, I want to delete rows after I apply filter to one columns with effecting the header

Hi can any one help me, I want to delete rows after I apply filter to one columns with out changing header And column names

Hi @Test_G, welcome to the forum! May you please post a few screenshots of what you are trying to achieve. I don’t fully understand what you are trying to achieve.