Delete rows in excel yet retain formulas

Help
1

Hi,

How can I remove rows directly from Table1 and yet retain the formulas for Table1?

For example, If ID in Table 2 = ID in Table 1, remove that row but the formulas for other rows should be retained. Copydatatable copies the datatable as string values. So I cant use that. I want the changes to be done on Table 1 itself. Please help @Palaniyappan @Dave

image
image1092Ă—388 14.6 KB

2

Are you able to use the “Delete Range” activity to accomplish your task?

This would leave your other cells/formulas in tact.

3

Not yet. I have to delete say 10 records all in different rows. I’m looking for some quick solution as my file is huge… around 200k

4

It sounds like this may potentially be a better solution for VBA.

This way you wouldn’t have to worry about passing strings back to UiPath and trying to maintain formulas when writing values back to Excel.

2 Likes
5

I agree with @chenderson - if you have to manipulate excel itself in any significant way, then it’s much better to use a VBA macro or VBScript with UiPath’s execute macro activity

1 Like
6

@Dave and @chenderson Is it possible to check the condition and delete the rows at the same time using VBA?

7

Yes, but you would not be able to use LINQ - you would only be able to use the features available in VBA or VBscript.

8

Like how @Dave? Can you please explain it?

I was working on the below solution, but I was not satisfied with it.

  1. Verify the condition in loop
  2. Get all the index values in an array
  3. In loop performed delete row with the index value fetched. (Here I added 2 to index to get the row position. Also reversed the array before deleting the rows, so that the rows will be deleted from bottom)

But this solution takes long time. It took around 3 hours to get the row index (file size 200K).
To delete the rows, I used “Insert/Delete row” activity in loop which saves the workbook everytime after deleting a row which ultimately increases the time :frowning:

9

I agree, that seems like it would take too long. Why do you have the requirement that you must manipulate it within excel?

I would use linq to get the values, then I would use a for each loop on the new table to update the formulas within the datatable (would take less than 1 second), then once you are all done with the manipulation, paste everything into excel.

If you do it within excel it’s going to take a very long time if you have a significant amount of data

1 Like
10

I don’t have anything to update here. I requirement is to remove rows that are processed from the master excel. I have the processed list in another excel. So i need to remove all those processed records from the master record.

11

I understand. If you have a requirement that it needs to be processed in excel, then it is going to take a very long time. I am proposing a much faster way of doing it, where everything is done within memory, then the final output is sent to excel.

2 Likes
12

@VanithaRajakumar

In conjunction with @Dave’s response, you can always write the datatable back to Excel, and then write the original formula to the entire range next to (or in) your table in Excel at once.

1 Like
13

@Dave What values can be extracted using linq? I’m not clear in this part. Please help

14

@chenderson Yeah. After trying all other workaround, this is the last option I can think of. I was bit hesitant to copy the formulas as I was afraid of data error. If there is no other way, then I have to copy the excel to datatable, delete the datarows and then paste the updated datatable to original excel and finally copy the formula.

15

@VanithaRajakumar I was basing it off of the solution provided here: Left excluding Join - #17 by VanithaRajakumar

This would give you a datatable that only contains rows if ID in Table 1 is NOT in Table 2. I’ll call this dt3

Then you would use a for each row activity on dt3.
Assign dt3.item(“Num1”) = “write your formula here”
Assign dt3.item(“Num2”) = “write your formula here”
Assign dt3.item(“Total”) = “write your formula here”

After iterating through dt3, write it back into excel. For your formula, you’ll want to use the row index + 2 to (like you mentioned) so it can reference cells properly

1 Like
16

Thanks @Dave… Let me try and get back

17

You sure that doesn’t store the formula as a string rather than a formula? In the past, you had to use the Write Cell to place a formula.

I would normally, place the formula in the first cell with the correct relative cells for the first cell, then use a Fill Down action (ie Select Range and ctrl+d). You can also do the FillDown in VBA/vbscript.

2 Likes
18

@ClaytonM if you use Excel write range it will store it correctly as a formula. If you use workbook write range it will store as text instead of a formula

1 Like
19

Good to know. Thanks.

20

Thank you all for your valuable feedback. I was able to perform my task in just 4 mins with all your help. Here is what I did,

  1. Read the Master Excel to Datatable.
  2. Based on the solution provided by @Dave in “Left excluding Join”, I removed the unwanted rows from Master Data Table
  3. Deleted the rows after the heading in Master Excel spreadsheet using VBA as suggested by @chenderson .
  4. Copied the updated Master Data Table contents to Master Excel spreadsheet.
  5. Copied the formula to the first cell of the columns containing formula, as per @ClaytonM
  6. And finally copied the formulas to all rows using “ExcelAutofill range” activity

Thanks a lot once again…!! :slight_smile: :slight_smile:

1 Like