Using a read range to read excel file and filter data table to only keep certain rows

Hello, I am using a read range to read a certain excel file which contains many rows and columns with headers. I have saved it to a dtMasterfile variable. I am trying to use a filter data table to keep all rows where column “Mandatory Selection” equals Agree. It only writes out the sheet name. Not sure what the issue is.


"Mandatory Selection" equals "Agree"

If this not works then

"Mandatory Selection" Contains "Agree"

or if the above not works it’s better to use Linq query in assign activity

filteredDataTable = dtInput.AsEnumerable().Where(Function(row) row.Field(Of String)("Mandatory Selection") = "Agree").CopyToDataTable()

Hi @mojo001

Might be it was not filtered properly,you are using the filter datatable right, instead of that use the below linq expression in assign activity,

→ Use read range workbook activity to read the excel and store in a datatable called dt_Input.
→ The use the below linq expression in assign activity.

- Assign -> Output_dt = Input_dt.asenumerable.where(Function(X) X("Mandatory Selection").toString.tolower.equals("agree")).Copytodatatable()

→ Then use the write range workbook activity to write the Output_dt to the other sheet in excel.

Hope it helps!!

Hi @mojo001

dtFiltered = dtMasterfile.AsEnumerable().Where(Function(row) row("Mandatory Selection").ToString().Equals("Agree")).CopyToDataTable()


Hi @mojo001 ,

Example Workflow

Step 1: Read Range

  • Drag and drop the Read Range activity.
  • Set the SheetName, Range (leave blank to read the whole sheet), and the output DataTable variable (e.g., dtMasterfile).

Step 2: Filter Data Table

  • Drag and drop the Filter Data Table activity.
  • Set the Input DataTable to dtMasterfile.
  • Set the Output DataTable to a new DataTable variable (e.g., dtFiltered).
  • In the Filter Rows tab, configure the condition to filter the rows where “Mandatory Selection” equals “Agree”:
    • Column: "Mandatory Selection"
    • Operation: =
    • Value: "Agree"

Step 3: Write Range

  • Drag and drop the Write Range activity.
  • Set the SheetName, DataTable to dtFiltered, and the StartingCell (e.g., “A1”).
  • Make sure to enable the AddHeaders option if you want to write the headers.

Thank you, this works!

1 Like

It’s my pleasure… @mojo001

Happy Automation!!

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