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…
Thanks for your ideas!