Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
1  
So when you delete empty rows in a defined range, would all the cells shift up within the range accordingly? Or would the entire row be deleted from the sheet? –  AdamL Jul 31 '13 at 1:20
    
The cells would shift up within the range, i believe excel does something similar –  sam Jul 31 '13 at 10:02
1  
Ok. Would it only be values that would be shifted up, or would it be required that formatting and/or formulae would also need to be shifted? –  AdamL Jul 31 '13 at 11:28
    
Yeh, the formula and formatting would need to shifted up too –  sam Jul 31 '13 at 12:53
    
In excel there is a similar feature - theexceladdict.com/_t/t031008.htm –  sam Jul 31 '13 at 12:54
show 1 more comment

1 Answer 1

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!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.