Are you looking to delete blank rows in Excel using power automate?

Power automate has a dedicated action to delete a row from a table, but the problem is this requires a key column with unique values to select the row to delete.

Unfortunately, your blank rows will not have any values so you can’t use a key column with the key value to delete the row.

This post is going to show you how to use the run script action instead to delete all blank rows in your table.

Create a New Office Script in Excel

Excel for Microsoft 365 allows you to write and record Office Scripts code using the typescript language.

These scripts can then be run from any flow using the Run script action in the Power Automate Excel connector.

You can build a generic script to delete any blank rows in a given table and then call this script from any flow. This avoids the use of the Delete a row action and bypasses the need for a key column and key value.

Here’s how to create a new Office Script.

1. Go to the Automate tab in the Excel ribbon.
2. Click on the New Script command. This will open the Code Editor on the right side of the sheet.

function main(
	workbook: ExcelScript.Workbook,
	tableName: string,
	colName: string
) {

	var blankRows: (string | number | boolean)[] = [];
	let myTable = workbook.getTable(tableName);
	let myCol = myTable.getColumnByName(colName).getRange().getValues();
	myCol.shift();

	for (i in myCol) {
		if (myCol[i][0] == "") {
			blankRows.push(parseInt(i));
		};
	};

	blankRows.reverse();

	for (i in blankRows) {
		myTable.deleteRowsAt(blankRows[i]);
	};
}

3. Paste the above Office Script code to the Code Editor.
4. Give the script a new name such as Delete Blank Rows.
5. Press the Save script button in the Code Editor.

This will save the script in your OneDrive and allow you to access it in Power Automate.

The code creates two input parameters for the Power Automate Run script action. The tableName and colName will allow you to reference a table and a column within the table by name.

An empty array called blankRows is created. This will be used to track the index number of each blank row in your table.

The first loop for (i in myCol) will loop through each row in the chosen column and test if the value is blank. If the value is blank then the index number of the row is added to the blankRows array.

Deleting rows from top to bottom will change the index number of each subsequent blank row, so the code will need to delete rows from bottom to top to avoid deleting the wrong rows.

This is done by reversing the order of the blankRows array using the reverse() method.

The second loop for (i in blankRows) will loop through the array containing each index of the blank rows and use the deleteRowsAt() method to delete the row based on the index number.

Since the index numbers are in reverse order, this deletes each blank row starting from the bottom of the table to the top.

Add a Run Script Action to Power Automate

Now you can use this script in any flow. All you need to do is reference the table name and any column name in the table to delete all the blank rows.

Here are the steps to use your Delete Blank Rows script in Power Automate.

1. Add the Run script action to your flow.
2. Select the Location of your SharePoint site where your Excel file is saved.
3. Select the Document Library where your Excel file is saved.
4. Select the Excel File from your document library.
5. Select the Delete Blank Rows script created previously.
6. Add the table name in the tableName input.
7. Add a column name from your table in the colName input.

📝 Note: The code checks if the rows are blank based on a single column, so be sure to select a colName that is blank only when the entire row is blank.

Now when you run the flow, it will remove any blank rows from your table!

Conclusions

Deleting blank rows in Excel is not possible due to the lack of a key value in the blank rows.

You can use the Run script action instead to run an Office Script code that will delete any blank rows in your table.

Have you come across this problem in your flows? How did you solve this? Let me know in the comments section below!