How to delete all the written rows in Excel

Hi, I’m using delete range activity but I wish to make it dynamic. is there anyway we can delete all the written rows only instead of giving specific range.

thanks in advance for helping me

1 Like

@Dmitri00007

You can use as below

DatatableVariable.Rows.Count

This will give you all the rows available

You can use as β€œA” + DatatableVariable.Rows.Count : β€œD” + DatatableVariable.Rows.Count

If you have header for the first row then you can add +2

Hope this will help you

Thanks

Hi @Dmitri00007

Please have a look at the below thread.

Hope this will be useful. Thank you.

1 Like

Hi

Hope the below steps would help you resolve this

  1. Say for example the number of rows and number of columns are dynamic

  2. In that case we can first read the excel with READ RANGE activity and get the output as datatable dt

  3. Now use a Assign activity like this

Int_rowcount = dt.Rows.Count

And another assign activity like this

Int_colcount = dt.Rows.Count

Where both int_rowcount and int_colcount are of type int32

  1. Then use a if activity like this

Condition will be like
int_colcount < 26

If the above condition is true it goes to THEN Block where use a Delete range activity
With range mentioned like this

β€œA2”+”:”+Convert.ToChar(65+(dt.Columns.Count-1) ).ToString+(dt.Rows.Count+1).ToString

If the if condition is false it goes to ELSE block where use a delete range activity with range mentioned like this

β€œA2”+”:”+”A”+Convert.ToChar(65+(dt.Columns.Count-1) - 26 ).ToString+(dt.Rows.Count+1).ToString

Reason to keep β€œA2” at start is to keep the header
If you don’t want then mention as β€œA1” instead
So that it delete the entire excel data

Cheers @Dmitri00007

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.