Huge Excel data

[cury]

Hi,
I need to filter and delete data from excel with formulas and append more data to it from another sheet. If I read the excel into data table(DT1), filter out (DT2) and rewrite back DT2 into the same sheet, problem is that I still have (DT1 - DT2) rows in the sheet. The data I need to append should be after DT2 rows. Any suggestions please. Many Thanks,

[rohith.prabhu]

Hello @cury ,

This is because you are overwriting the data into the same sheet.
(say sheet has 100 rows and after filtering you will write 50 rows but the other (filtered out data) 50 will be there in the sheet)

Is there any issue if you write it to another excel sheet?
Or try clearing the existing sheet and write the data into it.

Regards,
Rohith

[cury]

Thank you. There are some macros that other sheets use based on this sheet. So I cannot write to another sheet or delete this sheet. How do
I clear rows from Excel? I tried Insert/Delete. With huge data, it is dead slow deleting row after row.

Many Thanks,

[Mr.H]

Hi Bro.

Have 2 ways:

-1: Use excel application scope + Delete Range activity to delete range in excel sheet before write new data
-2: Use macro to clear data before write new data

[cury]

Thanks @Mr.H and @rohith.prabhu

[rohith.prabhu]

@cury you can follow the methods suggested by @Mr.H .

Also you can try : create a template (excel with only headers and Macro associated to it).
So every time after filtering data table use copy file activity and copy the template file to your output folder and write the data into it (there’s no need to delete / clear anything).

[cury]

Wow! Another great idea!
I will take whichever works faster.
Thanks again!!!