How to delete rows in the same sheet (Excel)?

Help
, , ,
1

For instance, I would like to delete rows where cell “Amount”= 0 or “Amount”= Empty, from Sheet1:

Input:
image

Output Expected:
image

I tried using the method below but the filtered rows never were deleted…
image

Note: I cannot use unofficial activities

Regards,

2

@Tana20 - Can you please show your filter Wizard?

3

Here you go:

image

I think the filter works, but it is overwriting the previous data …

4

2020-11-18_093058
“Amount” = “” is right. Not Empty

1 Like
5

@Tana20 - Also check the post below

6

We can definitely do that! Here’s how

  1. Wrap everything up in an excel application scope
  2. Read range and output to a DataTable
  3. Assign the first row number to a variable i
  4. For each row in the DataTable
  5. Check If the Amount is 0
    True - Use the Insert/Delete Row Activity to delete a row using the row number variable
    False - Increment the row number variable and go to the next row in the DataTable

NOTE - We do not Increment the row number variable after deleting a row because if we delete a row, the next row moves up

Here is a screenshot of the workflow I would use to delete rows where the Amount Field had 0 in it

image
image783×1073 22.7 KB

1 Like
7

Thanks, @Mr_Meeseeks, but since I have to process files with more than ~20K rows it is difficult to use loops; however I’m curious about how you used the activity “Insert/Delete Rows”…my understanding is that it requires a starter position which must be provided by a number…

8

Absolutely correct! Before looping through each row I assigned a variable equal to 2 for the row number and for the Position property of the Insert/Delete Rows activity I am plugging that variable in.

The Write Range activity would overwrite whatever data was in sheet 1. This is how I would expect the worksheet to look after performing the Write Range activity.
image

Invoice 4 is overwritten and Invoice 5 is seen twice

Would you consider Writing your filtered range to a new sheet and then replacing it? I agree that loops wont be very efficient for 20k+ rows :slight_smile:

You could also use the Insert/Delete Rows activity to delete all of the rows in the worksheet, then perform the Write Range activity using your filtered range on the cleared sheet

1 Like
9

@Tana20 - if its 20K rows then you need a LINQ query.

1 Like
10

@Tana20 - Here you go…

image
image518×610 16.7 KB

LINQ Query in the Assign activity

dtInput.AsEnumerable().Where(Function(row) Not(row(“Z”).ToString.Trim.Contains(“0”) or row(“Z”) Is DBNull.Value Or row(“Z”).Equals(“”) )).CopyToDataTable

Input

image
image153×516 2.59 KB

Output
image

3 Likes
11

@prasath17 Awesome! I was trying to understand how to use LinQ! :slight_smile: I just realized that the approach provided by @Mr_Meeseeks will help me in most cases and LinQ will improve the performance of heavy transactions. Thanks to both of you

12

@Tana20 - Glad it helped you. Did you already ran this against your 20K rows file?

I had 36K rows file, Filter table taking close 10-15 mins to run(along with the other activities)…Later I changed it to LINQ (after learning thru Forum) my job ran in less than 2 mins.

You refer this post for future filtering queries…

Also this

1 Like
13

Thanks for sharing that with me, and yes, I was almost there, I was just missing a parameter, but your script did the trick, it ran in 1m for 15K rows! :grin:

1 Like
14

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