Search Item and Delete Row in excel

Hi there,

I have a query, if anyone can assist

I am required to search an item on a search bar from an excel sheet and if it is not located in the search bar

It would indicate in message box and delete it from the excel. I am unable to remove the item searched on the excel. Anyone can help?

@chting

Use the Read Range activity to read the Excel sheet into a DataTable
Use the Input Dialog activity to prompt the user for the item they want to search for. Assign the user’s input to a variable
Use a For Each Row activity to iterate through the DataTable

Inside the loop, add an If activity to check if the current row contains the search item Replace ColumnName with the actual column name where you want to perform the search.

use an If activity to check if the loop completed without finding the item. You can do this by introducing a boolean variable and setting it to True when the item is found inside the loop.
Is still False it means the item was not found, and you can delete it.

Finally Use the Write Range activity to write the modified DataTable

Cheers…!

Hi there,

Think there was a bit of miscommunication. Basically this is how my RPA works

  1. RPA will track items in email, and record all data in an Excel path. (Item number, quantity, PartID)
  2. RPA will use item number to input in search engine and search if data matches
  3. If item exist, it will proceed to record it in an Excel path
  4. If it does not, it will not proceed it will not record in the Excel path, and it will delete the row from the Excel path(Item number, quantity, PartID)
  • Get Outlook Mail Messages (or your email source)
  • For Each email
- Extract Item number, quantity, and PartID from the email

- Excel Application Scope (to open the Excel file)
  - Read Range (DataTable: dtExcelData)

- For Each Row (in dtExcelData)
  - If (row("Item number").ToString = extractedItemNumber)
    - Write Cell (Value: extractedQuantity, Cell: row("Quantity"))
    - Write Cell (Value: extractedPartID, Cell: row("PartID"))

- Filter Data Table (to filter rows with empty Item number)
- For Each Row (in filtered DataTable)
  - Delete Row (DataRow: row)
  • Save Workbook
  • Close Workbook