How can I replace the whole sheet in Excel

Help
1

I read an Excel sheet, then delete rows and finally write it back to the sheet.

Problem:
Using write range only the length of the new table gets replaced and on the bottom there are still rows from the old table. Is there a way to replace a sheet as a whole (including empty rows) or is there an option like “empty sheet”?

e.g.
Sheet 1
Row 1: A B C
Row 2: D E F
Row 3: G H I
Row 4: X Y Z
Sheet 2
Row 1: A B C
Row 2: D E F
Row 3: X Y Z

→ If I replace Sheet 1 with Sheet 2, I want only the values of Sheet 2 but I then see the row with X Y Z twice.

2

@Jizh,

After reading excel sheet, count the no. rows has data using DataTable.Rows.count .

Then use Insert/Delete Rows activity to delete all rows.

No.of Rows: pass value from above count
Position: 1
Change Mode: Remove
Sheet Name: your sheet name.

Or

Use ** Write Cell** activity and specify range for suppose: “A1:F500”
Value: “”

1 Like
3

Thanks for the reply. I’m still not getting the result I need.
In my workflow I first read the Excel table, then delete some rows and finally write it back to the Excel sheet. I would like to have the sheet to ONLY have the new values and no empty rows between them.

4

I found a really ugly workaround by using write cell with value “” on “A1:O6000” to clear the sheet before writing. Isn’t there something better?

5

@Jizh,

Could you please share workflow personally. I will check and let you know.

1 Like
6

Sure, thank you. The row to delete could also be in the middle.
Sequence1.xaml (17.4 KB)
Termine.xlsx (11.9 KB)

7

@Jizh,

Yes, first count the rows which contains data like: DataTableName.Rows.count and assign it to interger variable and say count.

In write cell, specify range like this: “A1:O”+count.Tostring

8

Let’s say the table length was first 4 rows.
A
B
C
D
Then I delete row B and write it back with a length of 3 rows. Wouldn’t that mean that I get the following?
A
C
D
D

9

@Jizh,

Yes, first count the rows which contains data like: DataTableName.Rows.count and assign it to integer variable and say count. (output: 4 )

In write cell, specify range like this: “A1:O”+count.Tostring.

Then it will write empty string between all cells in range between “A1:O4”. Is it ok for you ?

1 Like
10

Now I get it, yes that’s better than my hardcoded solution.

1 Like
11

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