How to Delete Blank Rows in Google Sheets

While working with Google Sheets, you might have to delete certain data cells from the worksheet, which can result in scattered blank rows across your worksheet. Having such blank rows in your sheet gives it an untidy and unprofessional appearance.

You can delete blank rows manually if you’re dealing with a small number of blank rows. However, in cases of multiple blank rows, you can either filter blank rows or sort blank rows and delete them.

Delete Rows Manually

Here’s how you can delete blank rows manually in your Google Sheets.

1. Open Google Sheets and go to your Data Sheet. 
2. Hold the CTRL key and click on the blank rows to select multiple rows. 
   
3. Right-click and select Delete Selected Rows. 
   
4. The blank rows are deleted from the sheet now.

Sort Data Sets and Delete Rows

It would be an absolutely tedious task if you had to manually select the blank rows from a data set containing a large number of rows. In such a case, you can choose to sort your data by name.

This way, the data will be listed in an alphabetical manner, and the blank rows will appear at the end of the dataset. You can then select the empty rows and delete them. 

1. Open your Google Sheet. 
2. Select your entire dataset. 
3. Click on the Data tab.
   
4. Go to Sort Range > Advanced Range Sorting Options.
   
5. Check the box that says Data has header row and click on Sort.
   

This will sort all the data sets except the header column in alphabetical order and the empty data cells will be moved to the bottom.

Now, scroll to the bottom of the datasheet. Click and drag your mouse pointer and select all the blank rows. Then, right-click and select Delete rows “X”– “Y”.

Filter Empty Rows and Delete 

Filtering empty rows is another preferred method to delete blank rows in Google Sheets. In this method, you can set filters based on any criteria to separate blank rows from the rows containing data and later delete the blank cells at the same time. Here’s how:

1. Open the Google Sheet with the data. 
2. Select the first column, then go to Data > Create a Filter. 
   
3. Now click on the icon beside the first column heading and click on Clear. 
   
4. Select (Blanks) and click on OK.
   
5. Now, only blank rows will be displayed. Select and delete the selected rows.
   

Delete Rows Using Google Script

You can also add a script through Google Apps Script for it to automatically remove all the empty rows. Here are the steps.

1. Open Google Sheet.
2. Go to Extention > Apps Script.
   
3. Clear the page and add the following code to it:

function deleteBlankRowsInRange() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 1; // Specify the starting row of your range
  var endRow = 16;  // Specify the ending row of your range
  var numRows = endRow - startRow + 1;

  var range = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
  var data = range.getValues();

  for (var i = data.length - 1; i >= 0; i--) {
    var row = data[i];
    var isBlank = true;

    for (var j = 0; j < row.length; j++) {
      if (row[j] !== "") {
        isBlank = false;
        break;
      }
    }

    if (isBlank) {
      sheet.deleteRow(i + startRow);
    }
  }

4. Modify the var endRow with reference to the number of total rows in your sheet. 
   
5. Click Save Project and click Run.
   
6. A dialogue box will appear, asking you for authorization. Here, click on Review Permission. 
   
7. Now, Select your email account.
   
8. Click on Advanced. 
   
9. Click on Go to Untitled project(unsafe).
   
10. Click on Allow.
    
11. Now, the script will run automatically, and the blank rows will be removed.