How to delete excel rows based on filter?

Help StudioX
,
1

i have an excel sheet and i need to filter column D with “2” then delete all the filtered rows

2

Hi @Ahmed_Raafat

To filter column D with the value “2” and delete the filtered rows in UiPath, you can follow these steps:

  1. Use the “Excel Application Scope” activity to open the Excel file.
  2. Within the scope, use the “Read Range” activity to read the data from the Excel sheet into a DataTable variable, let’s say dtData.
  3. Use the “Filter Data Table” activity to filter the DataTable based on the condition in column D. Set the filter condition to "[ColumnD] = '2'" (assuming the column name is “ColumnD”).
  4. The filtered DataTable will be stored in a new DataTable variable, let’s say dtFiltered.
  5. Use the “For Each Row” activity to iterate through each row in dtFiltered.
  6. Inside the loop, use the “Delete Row” activity to delete the current row from dtData.
  7. After the loop completes, use the “Write Range” activity to write the updated dtData back to the Excel file, overwriting the previous data.

Thanks!!

3

@Ahmed_Raafat

Welcome to the community

Use filter excel activity and then use delete rows activity

Cheers

4

can you drop ex. xaml. please?

5

There are two recommended approaches to achieve this:

:one: Read Excel → Filter DataTable (No LINQ)

  • Read the Excel sheet into a DataTable.
  • Use Filter Data Table activity with the condition:
Column D = "2"
  • Use the filtered result to remove those rows from the original DataTable.
  • Write the updated DataTable back to Excel.

:check_mark: Easier to understand and maintain
:check_mark: Recommended for beginners

:two: LINQ Expression (Faster & Optimized)

  • Read the Excel sheet into a DataTable.
  • Use a LINQ Assign to exclude rows where Column D equals "2".

Example:

dt = dt.AsEnumerable().
     Where(Function(r) r(3).ToString <> "2").
     CopyToDataTable()

:check_mark: Faster for large datasets
:check_mark: Cleaner and more efficient
:check_mark: Preferred for performance-critical automations

Conclusion:

  • Use Filter DataTable for readability and simplicity
  • Use LINQ when performance matters and the dataset is large
6

Hi @Ahmed_Raafat

  • Read the Excel sheet

  • Use Excel Application Scope → Read Range into a DataTable (e.g., dt).

  • Filter rows

  • Use Assign activity:
    dt = dt.AsEnumerable().Where(Function(row) row(“D”).ToString <> “2”).CopyToDataTable()

  • This keeps only rows where column D is not 2.

  • Write back to Excel

  • Use Write Range in the same Excel sheet (overwrite it) with the filtered DataTable.

7

Read Range to get the sheet into a datatable.

Filter Data Table to keep/remove the necessary rows.

Write Range back to the sheet.

8

You can’t do this, you’ll get an error when trying to modify the same datatable you’re looping through.

9

This is wrong. This is what the Filter Data Table activity DOES. It removes the rows you don’t want.

10

We can select options based on our requirement—either keep or remove them.

image
image783×467 21.1 KB

11

My point is that you have another step after the Filter Data Table activity that says “use those filtered result to remove those rows from the original DataTable” but you don’t do that. The Filter Data Table activity does that. Just designate the same datatable as DT1 and DT2 for the Filter Data Table activity.