How to Delete everything in excel sheet that does not contain specific data

Help Studio
, ,
1

I have multiple columns in excel sheet table and I want to look for the word “rice_chicken” and delete any other data that does not contain that word. Is there a way it’s possible to delete everything in else in the sheet? Thanks in advance.

2

@md.muhit,

Use VBA macro for this. Use Invoke VBA activity for this.

3

Is there any range for this activity ?? Or you want to check in every available cell that contains data?

I have tried below VBA code and it is working as expected

Below code will clear all the cells that does not contain the keyword rice_chicken. You can modify based on your requirement. Let me know if you are not familiar with using VBA in UiPath

Sub DeleteChicken()

Dim ws As Worksheet
Dim rng As Range
Dim row As Range
Dim found As Boolean

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

' Set the range to check
Set rng = ws.UsedRange

' Loop through each row in reverse order (to prevent skipping rows)
For Each row In rng.Rows
    found = False
    Dim cell As Range
    ' Check each cell in the row
    For Each cell In row.Cells
        If InStr(1, cell.Value, "rice_chicken", vbTextCompare) > 0 Then
            'No Action Needed
        Else
            cell.Clear
        End If
    Next cell
    
Next row

End Sub

4

I got similar one using chatgpt these codes shows as executed but does not delete anything

5

@md.muhit,

Can you share sample file for this.

6

1.Read Range
2.Filter Data Table
Keep Rows
Column1 CONTAINS “rice_chicken” (repeat for other columns).
3.Write Range

OR
1.Assign Activity
dtFiltered = dtInput.AsEnumerable().Where(Function(row) row.ItemArray.Any(Function(cell) cell.ToString.Contains(“rice_chicken”))).CopyToDataTable()

Then
2.Write Range

Optional
For VBA Code
Close the Excel file and Run your old code

7

Note : Sometime code Runs Succefully but due to open Excel , it doesnot Work.

8

@Shakib,

You can use Kill Process activity to kill the excel before your code to avoid this error caused by open excel.

9

Keep Kill Excel at the beginning. Also keep excel as VBA macro enabled