How to insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets

Question

How do I insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets?

Lets say in row 1 it says "A" and in row 2 it says "B", "C" in row 3, etc. I would like it to say "B" in row 3, and "C" in row 5, etc. from now on. Inserting a row underneath one by one is simple. I'm asking about inserting empty rows on a larger scale.

Answer 1

I think this requires a script, such as this one:

function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   var range = sheet.getDataRange();
   var values = range.getValues();
   for (var i = values.length; i>=1; i--) {
     if (values[i-1].join("")) {
       sheet.insertRowsAfter(i, 1);
     }
  }
}

The command getDataRange() selects the range containing data; but since it's possible you have empty rows between filled ones, there is a separate check for nonemptiness of rows: values[i-1].join("") evaluates to True only if some data is present. The offset i-1 is needed because the index in an array is 0-based, while the row numbers begin with 1. I use i for row number.

The rows are inserted from bottom up (i--), to avoid the situation where newly added rows move down the ones that are still to be processed.

---

To use this:

1. Go to Tools->Script Editor.
2. Copy-paste the script there.
3. Save, renaming to something descriptive like "Extra lines".
4. Click "Run" button.
5. At the first execution (only) you'll be prompted to authorize the script.

Answer 2

If selecting some cells in the spreadsheet:
1. Hold the ctrl key down.
2. Select the cells that you want to empty.
3. Once cells are selected, hit the backspace key.

If selecting all cells in the Spreadsheet:
1. Press Ctrl + a
2. Once all cells are selected, hit the backspace key.