Delete Excel Rows Without Change Excel Format

[ercan.nebiler]

Hi,

I have an excel file which have 30000 rows and 50+ columns. I have to delete some rows based on a filtering a column. Then i will append another datatable to this excel file. I don’t want to change format.

What is the most efficient way to do this?

Thanks.

[Anthony_Humphries]

You could try this:

1.) Use a Read Range only on the filter column (you need that much to know what to delete).
2.) Create a List(Of Int32) to hold row numbers.
3.) Add a For Each Row loop over the data you read from Excel with a variable set for the Row Index. In the loop, if the filter value matches what you want to delete, add the variable for the Row Index to the list.
4.) After the For Each Row loop, add an Excel Applicatoin Scope activity to open the workbook.
5.) Add a For Each activity to iterate over the list you created in the previous steps.
6.) In the loop, use a Delete Row activity (the one for Excel) and use the values in the list to delete each row you’ve identified that should not be in the workbook. You may have to add 2 to the variable, since the row indices start at 0, and you’ll have to account for the fact that Excel starts at A1 and your workbook probably has headers.

[Pratik_Wavhal]

Hi @ercan.nebiler

1. If you dont want to affect the Excel Format then 1st Read the Excel by preserving the format. This will create one data table. Do the operation on that datatable itself like i have shown below.
   

2. Write the updated datatable from which you have deleted certain rows depending on some column condition i.e., 1st step

3. Append the datatable to the excel where you have write the data in the 2nd step as i have mention.

Hope this help you to solve your query.
Make as a solution and like it if it helps you.

Happy Automation

Best Regards
Er Pratik Wavhal