Delete Empty rows in a colum on workbook.

(2) Share

Report

Posted on by Sormick

566

Hello,

I have a workbook with 3 columns: A B and C

The column A contains country names

The column B contains city names

The Column C contains the word "YES" or the cell is empty

Here is an example below:

PORTUGAL / LISBON / YES

SPAIN / MADRID /

ITALY / ROME / YES

FRANCE / PARIS /

CANADA / VANCOUVERT / YES

I need to delete the rows which which contains "empty cells" en column C

I want to keep only the rows with "YES" value on column C.

Sorry but i am a beginner. If possible give me some detailled examples.

Thank you

Categories:

Power Automate Desktop

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Matkje 22 on at

Like (1)

Report

Hey

If you mark it all with CTRL A, and click Find and Select -> Go To special -> Blanks -> Ok -> CTRL and the minus sign and click entire row on the pop up, you delete these rows.

You can use SEND KEYS to send this, there are shortcuts for accessing everything in Excel :-)

Best regards

Mathias Kjeldsen

Was this reply helpful? Yes No
Suggested answer

eetuRobo 4,204 Super User 2025 Season 2 on at

Like (1)

Report
You could use VBscript to do that. Set variable called "ExcelFilePath" where you have the file path

Then Run VBScript

VBscript:
' This code has been generated by AI. Original prompt: ' give me vbscript that removes all rows from excel thats column c is empty Dim objExcel, objWorkbook, objSheet Dim lastRow, i ' Create an instance of Excel Set objExcel = CreateObject("Excel.Application") objExcel.Visible = False ' Set to True if you want to see the Excel window ' Open the workbook Set objWorkbook = objExcel.Workbooks.Open("%ExcelFilePath%") ' Replace %ExcelFilePath% with the path to your Excel file ' Set the worksheet (1 means the first sheet) Set objSheet = objWorkbook.Sheets(1) ' Find the last row with data in column C lastRow = objSheet.Cells(objSheet.Rows.Count, 3).End(-4162).Row ' -4162 is the constant for xlUp ' Loop through the rows from the bottom to the top For i = lastRow To 1 Step -1 If IsEmpty(objSheet.Cells(i, 3).Value) Then objSheet.Rows(i).Delete End If Next ' Save and close the workbook objWorkbook.Save objWorkbook.Close ' Quit Excel objExcel.Quit ' Clean up Set objSheet = Nothing Set objWorkbook = Nothing Set objExcel = Nothing

Or you could read the Excel to ExcelData table and use Filter Data table to filter Column3 that equals YES. Then write it into a new Excel worksheet.

Was this reply helpful? Yes No
Sormick 566 on at

Like (1)

Report

Unfortunately on my company i am rectricted and i cannot use the VBScrip element.

I would prefer instead (if possible) something like loop into Row A and delete rows if cells empty on column C.

Is this possible?

Was this reply helpful? Yes No
Suggested answer

eetuRobo 4,204 Super User 2025 Season 2 on at

Like (2)

Report
You can then do it like this:

1. Get first free row on a column
2. Get empty cells from the C column where the end row is FirstFreeRowOnColumn - 1 (minus 1 since the first free row is an empty row and one before that is one that has some values)

That will produce table like this:

Then have a loop that does the delete row -action as many times as there are EmptyCells table rows. But loop backwards so delete from the highest row to the lowest.

Start:

Result:

Code to paste to your PAD flow:

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''YourPath''' Visible: True ReadOnly: False Instance=> ExcelInstance Excel.GetFirstFreeRowOnColumn Instance: ExcelInstance Column: $'''A''' FirstFreeRowOnColumn=> FirstFreeRowOnColumn Excel.GetEmptyCellFromExcel.GetEmptyCellsAbsoluteRangeAllEmptyCells Instance: ExcelInstance StartColumn: $'''C''' StartRow: 1 EndColumn: $'''C''' EndRow: FirstFreeRowOnColumn - 1 EmptyCells=> EmptyCells LOOP LoopIndex FROM EmptyCells.RowsCount TO 1 STEP -1 Excel.DeleteRow Instance: ExcelInstance Index: EmptyCells[LoopIndex - 1][1] END

Was this reply helpful? Yes No
Sormick 566 on at

Like (1)

Report

Thank you all for your support:. It worked perfectly!

Was this reply helpful? Yes No