Delete Excel rows with specific value

I want to remove the excel rows completely for which the STATUS column has some particular values. I have achieved usin g a seperate datatable and then writing it back to another sheet, but would like your inputs if possivble by .net code which I can use in Invoke Code activity.

Thanks in advance

1 Like

Hi @Yoichi ,

Request your valueable inputs

1 Like

Hey @Ritika_Singh

You can use .NET LINQ query for the same.

dt_Result = dt_Input.AsEnumerable.Where(Function(row) row("Status").ToString.Trim.Equals("Success")).CopyToDataTable

This is just a model query & you can customize it as required

Hope this helps


Thank you for the solution but I want it through a C# code or .net code , this way I have implemented but in this have to write into another sheet, but I want it in same sheet

1 Like

Hey @Ritika_Singh

Just write the resultant datatable into the same sheet using Write Range activity.

Pass the same sheet name in the above activity. It will update in the same sheet fulfilling your need.

Hope this helps.


Tried but did not get the expected result coz resultent dt has less rows than original so getting messed

1 Like


Hope the below steps would help you resolve this issue

Use a simple assign activity like this
Suppose if you want to have only the column status having value as “success”

dt = dt.AsEnumerable().Where(Function(a) a.Field(of String)(“Status”).ToString.ToUpper.Contains(“SUCCESS”)).CopyToDatatable

But before writing this to a excel with write range make sure that you have deleted the already records and then write on top of it
Else what will happen if the new datatable has less records and if being passed to write range activity, though it overwrites the extra records will be still there

So delete the records first and then write the data

For that use a INSERT/DELETE ROWS activity and pass
Noofrows - dt.Rows.Count+2
Position - 0

Then use a write range activity with dt and add headers property enabled

Hope this would help you

Cheers @Ritika_Singh

Hey @Ritika_Singh

If you use inner join - you may get less rows as you said.

Please try with left join type and the same will work.


Position - 1 and it is slow coz my excel has 30k records and it will keep increasing so this approach is slow for me

Well not really it won’t take much of time not more than 5-6 seconds for 30k records


if possivble by .net code which I can use in Invoke Code activity.

FYI, the following will work.

dt= dt.AsEnumerable().Where(r=>!(keywords.Any(s=>r["STATUS"].ToString()==s))).CopyToDataTable(); (2.9 KB)


If I dont want to use datatable like a script I am looking for which can be invoked from UiPath

The problem is data will keep increasing on this sheet now its 30k in next executions it will have more rows of data added to it

I have used this way but lil slow as my data is huge, I have a code to delete the excel sheet as below:
**Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook

xlApp = New Microsoft.Office.Interop.Excel.Application
wb = xlApp.Workbooks.Open(filepath)

xlApp.DisplayAlerts = False
TryCast(wb.Sheets(sheetname), Microsoft.Office.Interop.Excel.Worksheet).Delete()
xlApp.DisplayAlerts = True


Something similar to this code can I have with If condition and all to remove the rows directly from the excel sheet which meets the STATUS column conditions


Can you try the following sample? (15.3 KB)




Can you try to add Microsoft.Office.Interop.Excel to xlDeleteShiftDirection like Microsoft.Office.Interop.Excel.xlDeleteShiftDirection?


Have added the Excel reference still error

I tried but it is not removing the rows


Does the sample which I attached in the above post work in your environment?