Delete values except the particular word from a column in excel

Hi all,

Have a columns in excel ,below is the sample input example excel shown.

Wanted to remove the values from column A except the value starts from N and L.below is the sample output need.

I’m doing it reading the Excel sheet and storing it to a datatable,but the problem is the sheet taking more time to read.

Can anyone suggest me can we do it with any excel activity so that I can save the time.

@yashashwini2322

filteredRows=(From row In dtInput.AsEnumerable()
             Let value = row.Field(Of String)("A")
             Where value.StartsWith("N") OrElse value.StartsWith("L")
             Select row).CopyToDataTable()

I’m achieving this from linq query,but my question is there any excel activity in UiPath to save time,because the sheet is taking more time to extract as it contains macro and formulas

Hi @yashashwini2322 ,
Try this,

Output_DT = Input_DT.AsEnumerable().Where(Function(r) r.Item("ColumnA").ToString.toupper.StartsWith("L") or r.Item("ColumnA").ToString.toupper.StartsWith("N")).CopyToDataTable()

Regards,
Vinit Mhatre

@yashashwini2322

Use Filter activity in excel process scope

I can only filter it,how can I delete those ?

you can use “Filter Datatable” Activity

Input:
image

Output:
image

Need to achieve without datatable.

Hi @yashashwini2322

Can you try the below

Delete.txt (642 Bytes)

Input:

image

Output:

image

Cheers!!

@yashashwini2322

image

@yashashwini2322


DeleteRows.txt (394 Bytes)

How to pass particular column name

@yashashwini2322

Here you pass the column index

I’m facing this error

@yashashwini2322

Can you change the Sheet name in Vba code.In my case it is “Sheet1” mention your sheet name

Yes I changed,
One clarification I need, is this vb code deletes the complete row or the cell…?
I need to delete the cell of particular column
Which is except starts with N and L

@yashashwini2322

It deletes the row

Can it be possible to delete the cells?

@yashashwini2322

If you n number of columns if some cell does not start with N .You said you have to delete it means the below cell will become the cell Now.For example the bot deletes 2 nd column 3rd row then 2 nd column 4th row will become the 2 nd column 3rd…? or 2 nd column 3rd row will become empty…?between these two cases which case you want

Hi @yashashwini2322 ,

To shift the cell contents up after clearing them, you can use the xlShiftUp parameter with the Delete method of the Range object

Sub Delete_cell()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    Dim colIndex As Long
    
    ' Set the worksheet and column index
    Set ws = ThisWorkbook.Worksheets("Sheet2") ' Change "Sheet1" to your sheet name
    colIndex = 1 ' Column A
    
    ' Find the last row in the column
    lastRow = ws.Cells(ws.Rows.Count, colIndex).End(xlUp).Row
    
    ' Loop through each cell in the column starting from the second row
    For Each cell In ws.Range(ws.Cells(2, colIndex), ws.Cells(lastRow, colIndex))
        ' Check if the cell value does not start with "L" or "N"
        If Not (Left(cell.Value, 1) = "L" Or Left(cell.Value, 1) = "N") Then
            ' Clear the cell contents
            cell.ClearContents
            ' Shift cell contents up
            ws.Range(cell, cell).Delete Shift:=xlShiftUp
        End If
    Next cell
End Sub


Input:
image

Output:
image

Regards,
Vinit Mhatre