Remove Blank Lines in Google Sheets

Formula-based filtering with FILTER()

If you don't want to touch your original data at all, FILTER() is the simplest option:

=FILTER(A1:D100, A1:A100<>"")

This creates a new, filtered view of your data in whichever cell you put the formula - it doesn't delete or modify anything in the original range. A1:D100 is the full range you want to filter (adjust to match your actual data), and A1:A100<>"" is the condition: keep only the rows where column A is not blank. Change A1:A100 to whichever column reliably has data in every real row of your sheet, since FILTER() checks that condition column, not the whole row.

The result is a live, spilling array - if your source data changes, the filtered output updates automatically. That's the main advantage over deleting rows outright: nothing is destroyed, so you can always go back to the original range, and the filtered version stays in sync as you keep working. The tradeoff is that you now have two copies of your data on the sheet (original plus filtered), which isn't ideal if you specifically need the blank rows gone from the source range itself - for that, you'll want Apps Script.

Apps Script: deleting rows in place

If you want the blank rows actually removed from your original sheet, not just filtered into a new view, Google Apps Script handles it with a short script:

function deleteBlankRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var i = data.length - 1; i >= 0; i--) {
    if (data[i].join('') === '') {
      sheet.deleteRow(i + 1);
    }
  }
}

Here's what it's doing: getDataRange().getValues() pulls every cell in the used range into a 2D array, one row per entry. The loop then walks that array from the last row to the first (i = data.length - 1; i >= 0; i--), joining each row's cells together with join('') - if a row is entirely blank, joining its empty cells produces an empty string, and that row gets deleted withsheet.deleteRow(i + 1) (the +1 accounts for the array being zero-indexed while Sheets rows start at 1).

The backwards loop matters here for exactly the same reason it does in theExcel VBA version: if you delete a row while counting upward, every row below shifts up by one, but your loop counter doesn't know that happened - so it silently skips the row that just moved into the position you already checked. Counting downward avoids this entirely, since deleting a row only affects rows below the one you're currently on, and your loop has already moved past those.

Running the script

Apps Script is genuinely different from Excel's VBA in one important way: there's no macro-enabled file format to worry about, no local installation, and nothing tied to a specific device. Your script lives attached to the Google Sheet itself (or as a standalone project), runs entirely in Google's cloud, and works the same way whether you open the sheet on your laptop or your phone.

To use it:

1. Open your sheet, then go to Extensions > Apps Script.
2. Delete any placeholder code and paste the script above.
3. Click the Run button (▶).
4. The first time you run it, Google will ask you to review and authorize permissions - this is expected, since the script needs access to edit your spreadsheet, and Google requires explicit consent before letting any script (yours or someone else's) do that. Click through the authorization prompt with your Google account.
5. After that, the script runs immediately each time you click Run.

If you want this to run automatically rather than manually - say, every time the sheet is edited, or on a daily schedule - Apps Script supports triggers, configurable from the clock icon in the script editor. That's beyond what a one-off cleanup usually needs, but worth knowing it's there if blank rows are a recurring problem in a sheet you use often.

Comparing to Excel

If you're deciding between doing this in Sheets or Excel - or you regularly work in both - ourguide to removing blank lines in Excel covers the equivalent Go To Special and VBA approaches. The underlying logic (find blanks, delete backwards to avoid skipping rows) is the same in both tools; only the specific commands and menus differ.

Skip the spreadsheet entirely

If your data started as pasted text rather than something inherently tabular, you might not need a spreadsheet formula or script at all.Remove Blank Lines does the same cleanup directly on raw text, right in your browser, before it ever needs to touch a Sheet.

This guide is part of our full roundup offree tools for cleaning up messy text, covering duplicate lines, blank lines, extra spaces, and dirty CSV exports.

Frequently Asked Questions