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.
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
Use Filter activity in excel process scope
I can only filter it,how can I delete those ?
Need to achieve without datatable.
How to pass particular column name
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
It deletes the row
Can it be possible to delete the cells?
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:
Output:
Regards,
Vinit Mhatre