Hi everyone ,
My requirement is i have an excel with 4 sheets and i need to loop through sheets and delete all rows except headers
currently i loop through each sheet , read range and use insert/delete rows activity
inputs : noRows = dt.rows.count+1
position = 2 (bcoz need headers)
sheetname
but since a sheet contains 1000’s of data its taking tooo long to complete the activity
can u just me some alternate easy method @Palaniyappan @jack.chan @Yoichi
Thanks in advance
Hi @kavya.s16,
My suggestion is to read all excel sheets and import them into the datatable instead of doing this. Then create clones of these tables (takes only the headers) and paste them into a new excel.
Regards,
MY
Hi @muhammedyuzuak but i need the format of the headers same , if i paste in new excel , format will be changed
I understand. Then you can use macros.First of all, you can position it in excel and run it either with a robot or from the inside.
@Rahul_Unnikrishnan this one is also taking same amount of time to delete as insert activity 30 mins for deleting min 100 rows
@kavya.s16 Not sure about the background process in that activity… Did you tried 2 times??
i tried it once , for each sheet taking 20mins to delete rows
@kavya.s16 Check this attached workflow,
Uipath_ClearDataExceptHeaders.zip (17.3 KB)
Hope this may help you 
@kavya.s16 if you are trying to add some data as part of automation and you need to keep that template static, then can you keep a template with only headers? Before to execution you can copy that to desired folder and add the data. After processing you can delete the file or move to another folder.
So for each execution you can use the template with headers( By copying )
@Rahul_Unnikrishnan but i have an excel like say 6 sheets and i need to read/delete rows for only 4 sheets and not touch the remaining sheets , how can i use template in such scenario?
@kavya.s16 So the data in sheet5 and sheet6 is always static and you need to add data only to sheet1,2,3, and 4. Is that correct?
If yes, you can loop through 4 sheets. create a counter variable and in the write range use that counter to represent the sheet number.
for example “Sheet”+i.ToString. , if i is 2, then it will be Sheet2.
If the template layout is always static then better to keep a default template instead of deleting, as it will take more time based on the number of rows.
Use Clear Datatable activity to clear the Data.
@Manish540 this worked , thank you
@Manish540 this one has some issue , for the first run this is delete the rows correctly , but if the run the flow again and use append or write range its adding empty rows at the beginning
eg : In first sheet if 2 rows where there and i run the bot its fine and again if i run again its adding 2 empty rows at the top , if sheet contains 4 rows then 4 empty rows are added
@kavya.s16 Can you please replace old VBA folder with this attached new VBA folder from project location and let me know the results.
VBA.zip (218 Bytes)
Hope this may help you
@Manish540 Still its same 
@kavya.s16 I have used below code in the ClearContents.vbs file,
“Sub ClearContents(sheetName,rng)
Sheets(sheetName).Activate
ActiveSheet.Range(rng).Clear
End Sub”
Please check whether the code is matching with above code in the ClearContents.vbs file which you have in project location.
So I have used this code and tried to clear the sheets after that I used Append range to add new Datatable to the file, So it was working fine for me.
→ Please check if any empty data rows are their in the datatable which you are appending or write ranging to the excel.
→ If still issue exist try to use write range and give the “A2” as range and write datatable without headers.
Hi @kavya.s16 ,
You could Check the post below which might be an alternate solution for your Problem. You could also let us know if it is time convenient for your operation.
@Manish540 , actually i have an excel with many sheets in that i am reading only 4 sheets ,
2021 - SSC , SSC - 2020 and earlier , 2021 - SMC , SMC - 2020 and earlier
my requirement is the process runs only on feb of every year
there are paperno. column in all these sheets
and all the dates in the excel sheets need to be dd-MMM-yyyy format
