How to remove Specific rows and keep other rows in excel?


I am trying to get remove the (with respect to Column "F: column name is “Account”) rows containing values starting with “1” except “161905” and I want to remove the null value rows also.


Thank You in Advance.

Hi @AryanSingh

Use row(“F”).ToString.except(“161905”)

use remove data row activity with row(“F”).ToString.except(“161905”)

Try this

Ashwin S


I want to remove the values starting with “1” except “161905”. there are other values also like starting “8” and “4” etc.

So, Please guide me regarding this!!

Hy @AryanSingh,

You can try this in a for each row activity:

IF : (row(“Account”).ToString.StartsWith(“1”) And row(“Account”).ToString.Trim <> (“161905”)) Or row(“Account”).ToString.Trim.Equals("")

THEN delete row


Ok…Thanks Let me try. Will get back to you.

Thank You

Hi @AryanSingh,

Try below steps, hope this helps you.

  1. Read excel into datatable [ReadRange Activity]
  2. Filter data table using below conditions [Filter Datatable Activity]
    a. Select “Keep”
    b. Column 5 is not null
    c. Column 5 = 161905
    d. Column 5 “Does not Starts with” 1
  3. Save the filtered data table into Excel.

Please try this, if this doesn’t works we can look for other options.
Other options are store separately the datarow value of 161905 and after filtering we need to add back to data table using add data row activity.

1 Like


I am getting this error when i am using remove data row in the if activity then part

Please check.

This is wright, i did not take into account that when you try to delete rows when enumeration in a for each loop, you will get that error.

you can use @adheedhan solution. It is easier.


I hope this is your requirement …

Use below code in assign activity

tes.AsEnumerable().Where(Function(x) not String.IsNullOrWhiteSpace(x(“Account”).ToString) and (not x(“Account”).ToString.trim.StartsWith(“1”) or x(“Account”).ToString.trim.StartsWith(“161905”) ) and not x(“Account”).ToString.trim.StartsWith(“4”) and not x(“Account”).ToString.trim.StartsWith(“8”) ).CopyToDataTable

as well I attached xaml as well
Main.xaml (8.5 KB)

Check and let me know

1 Like


Ok…i am trying that one. Thanks


ok. Will try @adheedhan method if doesn’t work. Will try your method.

Thank you


Hello thanx for the steps, but when i am trying it this way then it is returning an empty table


I tried the value in double quotes also but not able to get any value.

Other filters are working fine. only this one giving empty table.


Please guide me regarding this. Now only need this filter to work.

Please use the column index. Column index starts from 0.


Still not working. Please check the image.


Is the column 5 is converted into Int32?
use below to convert a column to Int32.

You can do this by 2 approcah.

1 -
Building a data table with required columns and data type and storing the values in the created data table.
So while creating the datatable make the column to Int 32.

2 -
Use assign activity to convert the column data type.


dtClone.Columns(“DOB”).DataType = GetType(Int32)

So the filter would consider it as Int32 and filter it. By default the extracted data would be String.


But the initial table has like 15-16 columns. Will have build all the things. Is there any way to change the value to int32 without building the table?

Use the assign activity which i mentioned earlier.
You can change the datatype of each column of a datatable.
“yourDatatable”.Columns(“columnName”).DataType = GetType(Int32)


Ok…Thanks…Let me try.


Tried this method getting this error.


Detailed error message while debugging