I have a large Excel table that I need to do the following to:
Filter one column.
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…
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
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?
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!