How to delete a group of cells and shift the rest up?

Question

In MS Excel, it is possible to select any rectangular range of cells and delete it in such a way that the cells below the deleted range move up to fill up its place. This feature is helpful, e.g., when one has two lists side by side for comparison, and a record needs to be removed from one of two lists.

In Google Sheets, one can delete an entire row and then the contents below it move up. How to do the same with a part of a row, or a general rectangular block?

Answer 1

This is now a built-in feature of Google Sheets, so workarounds are no longer needed. Just select the range, right-click, and pick a suitable way of deletion:

Answer 2

Apparently, this requires copy-pasting:

1. Delete the block
2. Copy the data below it
3. Paste it to the empty space.

(The first step can be skipped, although this may increase the risk of pasting to a wrong position.)

---

If you find this tedious after a while (as I did), a script can do the same. The script below adds a menu item "custom / "delete and shift up", which removes any selected range in the matter described below.

(The script is a bit longer than I expected at first, because I wanted to avoid moving long columns of empty cells, while simultaneously covering the possibility that a user may select a range than goes outside of the current DataRange.)

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [{name: "Delete and Shift Up",  functionName: "deleteAndShiftUp"}]);
}

function deleteAndShiftUp() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var totalHeight = sheet.getDataRange().getHeight();
  var totalWidth = sheet.getDataRange().getWidth();
  var toDelete = sheet.getActiveRange();
  var firstRow = toDelete.getRow();
  var firstColumn = toDelete.getColumn();
  var lastRow = Math.min(toDelete.getLastRow(), totalHeight);
  var lastColumn = Math.min(toDelete.getLastColumn(), totalWidth);
  var height = lastRow-firstRow+1;
  var width = lastColumn-firstColumn+1;
  if (height>0 && width>0) {
    if (totalHeight>lastRow) {
      sheet.getRange(lastRow+1, firstColumn, totalHeight-lastRow, width)
           .copyTo(sheet.getRange(firstRow, firstColumn, totalHeight-lastRow, width));
    }
    sheet.getRange(totalHeight-height+1, firstColumn, height, width).clear();
  }
}

Answer 3

The following fits my needs:

// remove dups and empty rows
function removeDupRowsInActiveSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  //var data = sheet.getDataRange().getValues();                              // full range in which data is present
  var data = sheet.getRange("A2:"+global_colstr_last_data_col).getValues();
  var newData = [];
  for (var i in data) {
    if (data[i].every( function(val, i, arr) {return val === "" })) continue; // <-- fix to skip blank
    var row = data[i];
    var duplicate = false;
    for (var j in newData) {
      if (row.join() == newData[j].join()) {
        duplicate = true;
      }
    }
    if (!duplicate) {
      newData.push(row);
    }
  }
  sheet.getRange("A2:"+global_colstr_last_data_col).clearContent();          // sheet.clearContents()
  sheet.getRange(2, 1, newData.length, newData[0].length).setValues(newData);
}

source: https://developers.google.com/apps-script/articles/removing_duplicates with my own fix