Delete rows from workbook if 2 columns contains "Y"

there are 2 columns that contain Y values within them. Bot needs to search for those rows where those 2 specific column contains Y and delete those specific rows. Attaching sample file for ref: the column names are "
Sample File.xlsx (19.2 KB)
Tax Has Employee Tax" and “Tax Has Employer Tax”


two ways

  1. Use filter Excel activity and filter on columns required with Y

  2. Use delete visible rows

  3. Read data into datatable

  4. Use filter datatable activity and give condition as “Tax Has Employee Tax” equals “Y” and “Tax Has Employer Tax” equals “Y”

  5. Write the filtered data back to excel


Filter Data Table: Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled.:

getting this error in filter datatable activity


As per error looks like you copied the double quotes from here…please remove and re dd them

correct - "
wrong - “

If error persists please show a screenshot of how you are giving the values



FYI, another approach using LINQ

arrDr = dt.AsEnumerable.Where(Function(r) not (r("Tax Has Employee Tax").ToString="Y" AndAlso r("Tax Has Employer Tax").ToString="Y")).ToArray() (17.2 KB)


Dt = dt.AsEnumerable()
.Where(row => row.Field(“ColumnName1”) == “Y” && row.Field(“ColumnName2”) == “Y”)

@ayushi_jain3 try this

Code is just executing, but not deleting the rows

This is not working either @Praveen_Mudhiraj

same error even after changing the quotes

Try with this code in assign activity

dt = dt.AsEnumerable().Where(Function(r) Not (r("Tax Has Employee Tax").ToString = "Y" AndAlso r("Tax Has Employer Tax").ToString = "Y")).CopyToDataTable()

Hope this helps

Cheers @ayushi_jain3

Nope, still not deleting. Also, it is removing the column value"Y" but not the rows somehow

So there were 2 rows i =n sample flle that had “Y” values. In one row only single column had “Y” value but in other row, both the columns had the “Y” value. So it just deleted the first row where single column had the value, but not the other row. If you go through the result.xlsx you shared, You’ll get the idea.


Hmm no
If u just look at the expression it neglects the row which equals to value Y and get the rest of rows

dt = dt.AsEnumerable().Where(Function(row) NOT row(“Tax Has Employee Tax”).ToString.Equals("Y”) OR NOT row(“Tax Has Employer Tax”).ToString.Equals(“Y”)).CopyToDataTable()


So there are 3 things:

  1. It is not deleting the rows
  2. The rows where both the columns have Y in it, only one column deletes Y value and the other one is left.
  3. It is just removing Y from the columns , not the rows.

Attaching the input file, the result is in the result sheet and also attaching the code.

Request you to please take a look at it.

Thanks a lot in advance :slight_smile:
Sample File.xlsx (19.8 KB)
Main.xaml (11.1 KB)

dtinput.AsEnumerable().Where(Function(row) row.Field(Of String)(“Tax Has Employee Tax”) <> (“Y”) AndAlso row.Field(Of String)(“Tax Has Employer Tax”) <> (“Y”)).CopyToDataTable()

Can you try query it will remove the Y rows completely in the both columns


1 Like

Remove the double quotes and keep again at same place it will work

Let me know it’s working or not for you


This worked, Thanks @Praveen_Mudhiraj , I just have one added question here. everytime a new data comes and the workbook needs to be updated. what action should be taken in that case?

1 Like

Super then… Can you close this loop thn if work for you

you can use the append range work book activity to add the data to existing one or merge data table you can use


1 Like

append range workbook is just pasting data from next balnk column . I want it to be pasted by removing original data

Create a new workbook instead of appending
Because when you try to overwrite on top of the existing records it might leave the previous records as it is