Delete rows in a datatable except headers (excel)

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)

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.


Hello @kavya.s16

Please do as below. It will meet your requirement.

1 Like

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, (17.3 KB)
Hope this may help you :slight_smile:


@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.

1 Like

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. (218 Bytes)
Hope this may help you :slight_smile:

@Manish540 Still its same :slightly_frowning_face:

@kavya.s16 I have used below code in the ClearContents.vbs file,
“Sub ClearContents(sheetName,rng)
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.

1 Like

@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

  1. I need to that paper no. column , if that column has paperno. present,void or na i have move those to SMC/SSC - 2020 and earlier and the request other than these values in paper no. column in 202* - SSC/SMC and rename the sheets accordingly
    eg: 2021 - SMC ----- 2022 - SMC and SMC - 2020 and earlier to SMC - 2021 and earlier and so on

and all the dates in the excel sheets need to be dd-MMM-yyyy format