Skip to main content

Notifications

Power Automate - Power Automate Desktop
Suggested answer

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

(0) ShareShare
ReportReport
Posted on by 377
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:
  • Suggested answer
    Nived_Nambiar Profile Picture
    Nived_Nambiar 17,084 on at
    How to delete all rows if empty cells on column 2 Excel
     
    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!
  • Suggested answer
    VishnuReddy1997 Profile Picture
    VishnuReddy1997 2,214 on at
    How to delete all rows if empty cells on column 2 Excel
    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,526

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,907

Leaderboard