Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''D:\\Temp\\sampledata.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
SET EmptyCellsCleared TO False
LOOP WHILE (EmptyCellsCleared) = (False)
Excel.GetFirstFreeRowOnColumn Instance: ExcelInstance Column: 2 FirstFreeRowOnColumn=> FirstFreeRowOnColumn
Excel.GetEmptyCellFromExcel.GetEmptyCellsAbsoluteRangeFirstEmptyCellInColumn Instance: ExcelInstance SearchColumn: $'''B''' StartColumn: $'''B''' StartRow: 1 EndColumn: $'''B''' EndRow: FirstFreeRowOnColumn - 1 EmptyCellRowIndex=> EmptyCellRowIndex
IF EmptyCellRowIndex <> 0 THEN
Excel.DeleteRow Instance: ExcelInstance Index: EmptyCellRowIndex
ELSE
SET EmptyCellsCleared TO True
END
Excel.SaveExcel.Save Instance: ExcelInstance
END
Excel.CloseExcel.Close Instance: ExcelInstance
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Desktop\\Power Automate Desktop\\Practice\\Excel\\Positive.xlsx''' Visible: True ReadOnly: False Instance=> Input_ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: Input_ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> Input_ExcelData
Variables.FilterDataTable DataTable: Input_ExcelData FilterParameters: { ^['RULE', 'COLUMN', 'OPERATOR', 'VALUE'], [$'''Or''', 1, $'''IsNotEmpty''', $''''''] } FilteredDataTable=> UpdatedDataTable
Excel.AddWorksheet Instance: Input_ExcelInstance Name: $'''Sheet2''' WorksheetPosition: Excel.WorksheetPosition.Last
Excel.WriteToExcel.WriteCell Instance: Input_ExcelInstance Value: UpdatedDataTable.ColumnHeadersRow Column: $'''A''' Row: 1
Excel.WriteToExcel.WriteCell Instance: Input_ExcelInstance Value: UpdatedDataTable Column: $'''A''' Row: 2
Excel.DeleteWorksheet.DeleteWorksheetByIndex Instance: Input_ExcelInstance Index: 1
' vbscript to if Column B contains empty cell its should remove the coressponding row from the excel
Dim objExcel, objWorkbook, objSheet, lastRow, i
' Create an instance of Excel
Set objExcel = CreateObject("Excel.Application")
' Open the workbook
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Desktop\Power Automate Desktop\Practice\Excel\Positive.xlsx")
' Set the worksheet
Set objSheet = objWorkbook.Sheets(1)
' Find the last row with data in column B
lastRow = objSheet.Cells(objSheet.Rows.Count, 2).End(-4162).Row ' -4162 is the constant for xlUp
' Loop through each row in reverse order
For i = lastRow To 1 Step -1
' Check if the cell in column B is empty
If IsEmpty(objSheet.Cells(i, 2).Value) Then
' Delete the entire row
objSheet.Rows(i).Delete
End If
Next
' Save the workbook
objWorkbook.Save
' Close the workbook
objWorkbook.Close
' Quit Excel
objExcel.Quit
' Clean up
Set objSheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing