Power Automate

How to delete all rows if empty cells on column 2 Excel

(0) Share

Report

Posted on by Sormick

566

Hi,

On the column "B" of my Excel workbook i need to delete all rows only if the cells on the colum "B" are empty.

How can we do this on power automate desktop ?

Please find my screenshot of my automate.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

VishnuReddy1997 2,656 Super User 2025 Season 2 on at

Like (1)

Report
Hey Hi,

You can achieve this using 2 methods.Please find the solution.

Methods1:

Code:

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

Method 2:

You try with Vbscript by just using Run Vbscript action PAD as shown in below image.

Vbscript:
' 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

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

Was this reply helpful? Yes No
Suggested answer

Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (0)

Report
Hi @Sormick

You can try this approach as well.

Code you can copy from below

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

Hope it helps !

Thanks & Regards,
Nived N
LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs
Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you!

Was this reply helpful? Yes No