Clear all cells in excel sheet without deleting sheet

Help Studio
, ,
1

I need to clear all the cells in an excel sheet before writing a range back to the same sheet. The data is dynamic so there will be a different number of cells each time the bot runs. I tried delete range but I am not sure how to make it dynamic. Is there a way to do this? or maybe using some vb code?

1 Like
2

Hi @kasey.betts,

Use a Read Range activity (https://docs.uipath.com/activities/docs/read-range) first, which will return a DataTable as output.

Then, use an Assign activity to store the number of rows that the DT has (yourDataTable.Rows.Count)

Finally, when you use the Delete Range activity (https://docs.uipath.com/activities/docs/excel-delete-range), concatenate your last column with the varaible that has the number of rows.

Example: "A2:Z" + numberOfRows.toString

If the number of columns is also dynamic, let me know. Then, I share another possible solution.

Kindly mark this answer as solution if it helps you :slight_smile:

Thanks!

3

Thank you for this! The number of columns may also be dynamic.

1 Like
4

Hi @kasey.betts

Ok, so…

After reading the range and storing the number of rows, store the number of columns too (yourDataTable.Columns.Count)

Then, you can use this trick to convert a number to its respective letter and store it:

Finally, concatenate everything in the Delete Range activity. Example: "A1:" + lastColumnLetter + (numberOfRows+1).toString

Hope it helps you.

Kindly mark this answer as solution if it helps you :slight_smile:

Thanks!

5

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