Script for deleing blank rows in Google Spreadsheet

Here is a Google Apps Script for deleting blank or empty rows in Google Spreadsheet. Let us take the following example

As you can see in the above screenshot, some of the rows are blank and manually clearing them would take time. You can try the below script by selecting the first column. This script first identifies the rows for deletion by using the first column in the worksheet and then deletes each row from the bottom to top.

Please make sure to take a backup of your data before executing the script.

function myFunction() {
  var aRange = SpreadsheetApp.getActiveRange();
  var totalRows = aRange.getNumRows();
  var aSheet = SpreadsheetApp.getActiveSheet();
  rowCount = 1;
  index = 0;
  var rows=new Array();
  while (rowCount < totalRows){
    var cValue = aSheet.getRange(rowCount, 1).getValue();
    if(cValue){
       //
       }else{
         rows[index]=rowCount //store row position;
         index++;
       }
    rowCount ++;
  }
  rowCount = rows.length;
  while (rowCount > 0){
         aSheet.deleteRow(parseInt(rows[rowCount-1]));
         rowCount –;
         }
}

In Category: Technical

Ravi Shankar

A Software developer and blogger who is always looking to provide technical help to the wider community.

Show 5 Comments
  • jimson May 31, 2012, 3:21 pm

    Hey this code is not working!!!
    Check it…

  • jimson May 31, 2012, 3:21 pm

    Hey this code is not working!!!
    Check it…

  • kinjal dixit December 22, 2012, 9:49 am

    if this code shows an error ‘illegal character’ when saving and you have copy-pasted from the site, just delete the ‘-‘ in the line

    rowCount -;

    and then type in ‘–‘ so that the line is

    rowCount –;

  • kinjal dixit December 22, 2012, 9:49 am

    if this code shows an error ‘illegal character’ when saving and you have copy-pasted from the site, just delete the ‘-‘ in the line

    rowCount -;

    and then type in ‘–‘ so that the line is

    rowCount –;

  • jck October 11, 2015, 2:05 am

    Hey Ravi
    TY for this script, I modified it a bit so it can works good:

    var aSheet = SpreadsheetApp.getActiveSheet();
    var totalRows = aSheet.getLastRow();
    var rowCount = 1;
    var index = 0;
    var rows = new Array();
    while (rowCount 0){
    aSheet.deleteRow(parseInt(rows[rowCount-1]));
    rowCount–;
    }

Leave a Comment

Get your free copies of the following tech guides by joining the Digital Answers mailing list.