Deleting the rows from excel

​@Abisha Gopalan Try Excel Advanced > Filter Table to filter rows matching your condition, then Excel Advanced > Delete Range on the filtered visible rows.

Hi ​@Abisha Gopalan,

There are plenty of different approaches to achieve this.

1. You can use line script like VBA or Python (Faster and reliable)
2. Delete in ranges, not row‑by‑row

              i) Option A—Use the Excel Advanced →  Delete row or column action with a range

Instead of looping, compute a contiguous range (e.g., 2:5000) or multiple ranges and call Delete Row by range (e.g., 1:5, 10:15) to remove them at once. The action supports bulk deletion by specifying ranges. 

             ii) Option B—Filter first, then delete visible rows (VBA from AA or AA steps)

Drive Excel via AA:

Open workbook (Excel Advanced): Apply a Filter (Excel Advanced →  Apply filter or use Keystrokes if  needed). Call a small VBA macro (above) via Run Macro (or inject with AA’s “Run script” approach) to delete visible rows at once. This is often faster than AA deleting row‑by‑row because Excel does the heavy lifting internally.

3. Treat Excel as a database; bulk keep or bulk delete with SQL

   Connect to the workbook with OLE DB/ADO and use SQL to select only the rows you want (write out filtered rows to a new sheet), then replace the original. It avoids per‑row deletes and scales better on huge files

4. Use Data Table in AA

   Import the sheet into a Data Table, use filter/loop just to build a new Data Table containing only the rows you want to keep, and then write it back (overwrite the sheet). This replaces deletion with a single write operation. Community experts recommend this for large datasets where direct deletes are slow.

5. If you’re removing blank rows only

   AA published a step‑by‑step using Excel Advanced → Remove blank rows, which deletes blank rows across a range without manual loops. Useful if your condition is a blank row. 

​@Abisha Gopalan If you have this much data in Excel, you're probably using the wrong tool. Excel is not a database. 

That being said, using the Excel Basic package works significantly faster than the Excel Advanced package because it works directly with the file, not through a conduit to Excel itself. Consider that first. 

The other possibility is to read the contents of the Excel file into a data table variable and do your manipulation there. That is dangerous though because the row indices must be carefully manipulated due to rows being deleted. 

Both of those will significantly speed up Excel operations -- but not as much as using the right tool for the job: an actual database. 

Hi ​@Padmakumar ,

Thanks for the reply and i m trying to use VB script to run the activity instead of loop.

however, i have already tried converting as data table that is even worse, it takes very very long.

Thanks for the confirmation. Typically the Data Table operations are faster when compared to Excel's. Also, before trying the VB script, I would recommend trying the Excel As DB option too through the Database package. 

​@Abisha Gopalan I must still recommend the Excel Basic package.

• You won’t have to significantly change your code
• You won’t have to fight for the right connection string to do the Excel > Access > ODBC dance that is using Excel as a database (it’s not!)
• It will be easier than using a data table and having to worry about row indices and whether or not to increment them after deleting a row.

For the sake of sanity, check Excel Basic. Unlike Excel Advanced, Basic opens the file directly and manipulates it at the XML level. It’s very fast -- nearly as fast as a data table -- and you don’t have to write the data to the Excel file at the end, making it even faster!

Excel Basic can be an option only if the file is in .XLSX and the size is less than 30 MB. 

​@Padmakumar You're right, there is a documented limit of 30MB for file size. That may not be so much of a limit. I have a 20K line 6 column dataset in XLSX and the file size is only 898KB. 😊

I'll have to see if I can build a really big file and see if that 30MB limit is a hard limit or just a theoretical limit. 

To be frank, I haven't even seen an Excel file more than 10 MB in size in my life. However, since the ask was to work with a larger data set (the actual size is unknown), I was giving a heads-up.

​@Padmakumar Okay, the docs say the max is 30GB, but I created a 100MB XLSX file and Excel Basic opened the file! Just make sure you have enough RAM as opening that file caused a big RAM usage spike.

This is exactly why databases are a better choice for large datasets. They’re designed for this! Excel is not.

By the way, you can also open an Excel file in Access. That links to the original Excel file and doesn’t use as much RAM. You can then save the Access database and use standard Database package commands to query and manipulate the data. Much easier than the XLSX > JET > ODBC bridge of trying to use Excel as a database.