Delete the row from Excel file which are not highlighted without affecting the formula

Help Studio
, ,
1

Hi Team, I have a scenario where i have a excel file with data.
the portion of the data is highlighted only first 2 columns.

I need to delete all the rows from excel which are not highlighted without affecting formula.

image
image762×335 23.1 KB

From the above image need to delete only 10,11,16 n 17th row

TIA

2

where are you struggling?
You need entire logic or just the part where you are struggling?

3

Hi @MEGHA_cs
Install the namespaces

  • Microsoft.Office.Interop.Excel
  • System.Runtime.InteropServices
  • System

Use invoke code activity

Dim excelPath As String = "C:\Users\\Documents\UiPath\ForumSolutions\test.xlsx" ' Path to the Excel file
Dim sheetName As String = "Sheet1" ' Sheet name
Dim highlightColor As Long = 65535 ' Example: Yellow (adjust this to your desired highlight color)
Dim outputPath As String = "C:\Users\\Documents\UiPath\ForumSolutions\tes.xlsx" ' Output file path

' Create Excel application and workbook objects
Dim excelApp As New Microsoft.Office.Interop.Excel.Application
Dim workbook As Microsoft.Office.Interop.Excel.Workbook = CType(excelApp.Workbooks.Open(excelPath), Microsoft.Office.Interop.Excel.Workbook)
Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = CType(workbook.Sheets(sheetName), Microsoft.Office.Interop.Excel.Worksheet)

' Get the last used row
Dim lastRow As Integer = CType(worksheet.Cells(worksheet.Rows.Count, 1), Microsoft.Office.Interop.Excel.Range).End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row

' Create a new workbook for the filtered data
Dim newWorkbook As Microsoft.Office.Interop.Excel.Workbook = CType(excelApp.Workbooks.Add(), Microsoft.Office.Interop.Excel.Workbook)
Dim newWorksheet As Microsoft.Office.Interop.Excel.Worksheet = CType(newWorkbook.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet)
Dim newRow As Integer = 1

' Loop through rows in the sheet
' Loop through rows in the sheet
For i As Integer = 1 To lastRow
    ' Get the cell in the first column
    Dim cell As Microsoft.Office.Interop.Excel.Range = CType(worksheet.Cells(i, 1), Microsoft.Office.Interop.Excel.Range)
    
    ' Explicitly cast the Interior.Color property to Long
    Dim cellColor As Long = CType(cell.Interior.Color, Long)
    
    ' Check if the cell color matches the highlight color
    If cellColor = highlightColor Then
        ' Copy the entire row to the new worksheet
        CType(worksheet.Rows(i), Microsoft.Office.Interop.Excel.Range).Copy(CType(newWorksheet.Rows(newRow), Microsoft.Office.Interop.Excel.Range))
        newRow += 1
    End If
Next


' Save the filtered data to a new Excel file
newWorkbook.SaveAs(outputPath)
newWorkbook.Close()
workbook.Close(False)
excelApp.Quit()

' Release resources
System.Runtime.InteropServices.Marshal.ReleaseComObject(newWorksheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(newWorkbook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)

It works for me!!
Hope this helps

4

Alternatively to @Sanjay_Bhat proposal you could

  • loop through your table
  • use “Get Cell Color” activity
  • based on returned value delete row

Cheers