Google-sheets – Remove empty rows in Google Spreadsheets

google sheets

Is there a way to in Google Spreadsheets to remove / delete empty rows?

Ideally I'd like to be able to select the columns to check.. ie. Select columns D, E and remove all the empty rows from them or being able to select A1:E50 and delete all empty rows in that range, instead of doing it sheet wide.

Best Answer

I've built two solutions, the difference is whether it removes all empty cells in the selected range or only the ones where all cells in a row (in the selection) is empty.

Both solutions require an additional sheet "ScriptSheet", which you can hide.
Both solutions shift the remaining cells up, including the ones below the selections.

You can try both here, they can be found in a custom menu called "Scripts". Make a copy of the spreadsheet.

Solution 1

This script only removes cells if an entire row of cells in the selection is empty.

function removeEmptyRowsInSelection(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var scriptSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ScriptSheet");
  var sheetLastRow = sheet.getLastRow();
  var range = sheet.getActiveRange();
  var rangeFirstRow = range.getRow();
  var rangeLastRow = range.getLastRow();
  var rangeFirstCol = range.getColumn();
  var rangeLastCol = range.getLastColumn();
  var rangeCols = rangeLastCol - rangeFirstCol + 1;
  var maxRows = sheet.getMaxRows();
  var maxCols = sheet.getMaxColumns();
  var scriptSheetMaxRows = scriptSheet.getMaxRows();
  var scriptSheetMaxCols = scriptSheet.getMaxColumns();
  var insertRows = maxRows - scriptSheetMaxRows;
  var insertCols = maxCols - scriptSheetMaxCols;
  var currScriptRow = 1;
  
  scriptSheet.clear();
  
  if (insertRows > 0){
    scriptSheet.insertRows(1, insertRows);
  }
  
  if (insertCols > 0){
    scriptSheet.insertCols(1, insertCols);
  }
  
  
  for(var i = rangeFirstRow; i <= rangeLastRow; i++){

    var currRange = sheet.getRange(i, rangeFirstCol, 1, rangeCols);
    var currValue = currRange.getValues().join();
    var emptyValue = '';
    for (j = rangeCols; j > 1; j--){
      emptyValue = emptyValue + ',';
    }
    if(currValue != emptyValue){
      currRange.copyTo(scriptSheet.getRange(currScriptRow, 1));
      currScriptRow++;
      var test;
    } 
  }
  
  
  
  for(var currRow = rangeLastRow + 1; currRow <= sheetLastRow; currRow++, currScriptRow++){
    sheet.getRange(currRow, rangeFirstCol, 1, rangeCols).copyTo(scriptSheet.getRange(currScriptRow, 1));
  }
  
  var clearRange = sheet.getRange(rangeFirstRow, rangeFirstCol, maxRows, rangeCols);
  var scriptSheetRange = scriptSheet.getRange(1, 1, scriptSheet.getLastRow(), scriptSheet.getLastColumn());
  var pasteRange = sheet.getRange(rangeFirstRow, rangeFirstCol);
  
  clearRange.clear();
  scriptSheetRange.copyTo(pasteRange);
}

This function is much slower than the other one. It went through about 1000 lines in the first of three columns before canceled by the six minute limitation.

Solution 2

This solution removes all empty cells in the selection.

There is two functions in this one, one two find the first empty cell in a column. This function (as perhaps the rest as well) is probably possible to write more efficient.

function removeEmptyCellsInSelection(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var scriptSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ScriptSheet");
  var sheetLastRow = sheet.getLastRow();
  var range = sheet.getActiveRange();
  var rangeFirstRow = range.getRow();
  var rangeLastRow = range.getLastRow();
  var rangeFirstCol = range.getColumn();
  var rangeLastCol = range.getLastColumn();
  var maxRows = sheet.getMaxRows();
  var maxCols = sheet.getMaxColumns();
  var scriptSheetMaxRows = scriptSheet.getMaxRows();
  var scriptSheetMaxCols = scriptSheet.getMaxColumns();
  var insertRows = maxRows - scriptSheetMaxRows;
  var insertCols = maxCols - scriptSheetMaxCols;
  var currRow;
  var pasteCol = 1;
  
  scriptSheet.clear();
  
  if (insertRows > 0){
    scriptSheet.insertRows(1, insertRows);
  }
  
  if (insertCols > 0){
    scriptSheet.insertCols(1, insertCols);
  }
  
  
  
  for(var i = rangeFirstCol; i <= rangeLastCol; i++, pasteCol++){
    currRow = rangeFirstRow;
    for(var j = rangeFirstRow; j <= rangeLastRow; j++){
      var currValue = sheet.getRange(j, i).getValue();
      if(currValue != ''){
        var scriptSheetEmptyRow = getColumnLastRow(pasteCol, scriptSheet);
        sheet.getRange(j, i).copyTo(scriptSheet.getRange(scriptSheetEmptyRow, pasteCol));
      }
      currRow++;
    }
    var scriptSheetRow = getColumnLastRow(pasteCol, scriptSheet);
    for(; currRow <= sheetLastRow; currRow++){
      sheet.getRange(currRow, i).copyTo(scriptSheet.getRange(scriptSheetRow, pasteCol));
      scriptSheetRow++;
    }
  }
  
  var clearRange = sheet.getRange(rangeFirstRow, rangeFirstCol, maxRows, rangeLastCol - rangeFirstCol + 1);
  var scriptSheetRange = scriptSheet.getRange(1, 1, scriptSheet.getLastRow(), scriptSheet.getLastColumn());
  var pasteRange = sheet.getRange(rangeFirstRow, rangeFirstCol);
  
  clearRange.clear();
  scriptSheetRange.copyTo(pasteRange);
}

function getColumnLastRow(column, sheet){
  var row = 0;
  do{
    row++;
    var value = sheet.getRange(row, column).getValue();
  }while(value !== '')
    return row;
}

This solution is obviously faster the the previous one. It went through 1555 rows, 3 columns, in 02 minutes and 40 seconds.

I hope they can help!

Related Topic