// Sheets · Apps Script

Delete empty rows in Google Sheets.

A short Apps Script that deletes every fully-empty row in the active sheet, with a bottom-up loop so deleted rows do not shift indices beneath you mid-scan.

“I have a spreadsheet full of empty rows scattered through the data and I want to remove all of them at once without clicking through them manually.”

The script

copy · paste · trigger

deleteEmptyRows.gs

Apps Script

// Delete every fully-empty row in the active sheet.
// Run from Extensions > Apps Script > Run.
function deleteEmptyRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();

  for (var i = lastRow; i >= 1; i--) {
    var values = sheet.getRange(i, 1, 1, lastCol).getValues()[0];
    var isEmpty = values.every(function(cell) { return cell === ''; });
    if (isEmpty) {
      sheet.deleteRow(i);
    }
  }
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why the loop runs backward

When you call deleteRow(i), Sheets immediately renumbers every row below it. Row 5 becomes row 4, row 6 becomes row 5, and so on. If your loop is running top-down and you delete row 3, your counter advances to row 4 -- which is now the old row 5. The old row 4 is never checked. The first time I hit this, I ended up with alternating empty rows still in the sheet and spent ten minutes wondering why the script had done anything at all.

Starting at getLastRow() and decrementing avoids this entirely. Deleting a row only renumbers rows below it, and below is where you have already been. Every row gets inspected exactly once.

Reading the whole row at once

getRange(i, 1, 1, lastCol) fetches a single-row range that spans every column the sheet uses. getValues() on that range returns a two-dimensional array, so [0] pulls out the first (and only) inner array -- a flat list of cell values.

Passing that array to every() with a strict equality check against an empty string is the fastest way to decide emptiness in pure JS. A cell containing a space, a zero, or a formula that evaluates to blank will not be treated as empty by this check. That is usually the right behavior: a cell with content is not an empty row, even if it looks blank on screen. If you need to catch formula-blanks too, swap the check to return String(cell).trim() === ''.

Pasting and running the script

Open the sheet, go to Extensions > Apps Script, and replace any boilerplate in the editor with the function above. Click Save, then Run. The first run will ask for permission to manage your spreadsheet -- that is a normal OAuth consent for any script that writes to Sheets.

For a sheet with thousands of rows, the script will finish in a few seconds. Apps Script has a six-minute execution limit per run, and deleteRow calls count against your Sheets API quota, but you would need to be deleting tens of thousands of rows before either limit becomes relevant in practice. If you do hit quota, split the work: run on a selection rather than the full sheet by replacing getLastRow() with a fixed upper bound and adjusting the loop start.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

The script ran but some empty rows are still there. What happened?: Those rows probably contain something invisible -- a trailing space, an apostrophe used to force text formatting, or a formula returning an empty string. Select one of the surviving rows, open a cell, and check the formula bar. If there is anything at all in there, the every() check correctly left it alone. Change the emptiness check to return String(cell).trim() === '' to catch spaces, or delete the cell contents manually for formula-blanks.
Can I undo this after the script runs?: Yes. Ctrl+Z (or Cmd+Z on Mac) works after running an Apps Script, as long as you have not closed the spreadsheet tab. Sheets bundles all the deleteRow calls into a single undo step, so one undo restores everything. If you closed the tab, check Version History under File -- Sheets saves a snapshot before script execution.
How do I run this on a specific sheet in the workbook, not the active one?: Replace getActiveSheet() with SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName'). Use the exact tab name, case-sensitive. If the name has spaces, that is fine -- pass it as a plain string.
Will this work if my sheet has merged cells?: Partially. deleteRow on a row that is part of a vertical merge will unmerge the cells and may leave unexpected blank cells in the rows above. Inspect any merged regions before running, and unmerge them first if they span rows you want to delete.