How to Delete Blank Rows in Excel with Power Automate

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();

	for (i in myCol) {
		if (myCol[i][0] == "") {


	for (i in blankRows) {
  1. Paste the above Office Script code to the Code Editor.
  2. Give the script a new name such as Delete Blank Rows.
  3. 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!


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!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Related Articles



  1. Neal

    Hello John,

    I created a simple sheet only to try this out, but I always get:
    ‘Line 9: Cannot read properties of undefined (reading ‘getColumnByName’)’
    I had only one sheet called ‘Sheet1’ and only one column called ‘A’

    I am new to TypeScript.

  2. Michael


    Thank you for that, this is what I was looking for.

    This also works with dynamic files, where you create the table within the flow first.
    However I had to delay the Script and the table creating by one minute by using delay action.

    With this Script I can now delete the empty rows immediately after creating the table in a new file, which is much faster then using a condition check on each row to see if a row is empty.


Get the Latest Tech Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in tech!