1

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.

  • What do you mean by "simultaneously"? What are you trying to do? What have you already tried that didn't work. As it stands, your question is not very clear. – ale Apr 23 '15 at 12:52
  • Since you cross-posted this on Stack Overflow (something's that's frowned upon) but expanded that post, I've added your changes there to this question here so that it's clearer. – ale Apr 23 '15 at 19:02
2

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.

script

  • I would just execute from the script page, by pressing "Run" there. I suppose this is not the kind of operation you do many times on the same spreadsheet. – user79865 Apr 26 '15 at 23:59
  • The function now runs until it exceeds maximum limit, and when I run it again it just applies itself again to the ones that have already been applied. But essentially it does work. Thanks. – Sirudol Apr 27 '15 at 0:41
  • I see: the spreadsheet is so large that the computation times out. When this happens, you can replace "values.length" on line 6 by the number of the last row that was left unprocessed, and run again. – user79865 Apr 27 '15 at 0:43
0

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.

  • I know how to erase cells, I'm wondering how to add empty cells in between. Totally different. – Sirudol Apr 22 '15 at 6:09
  • 1
    You should have stated this in your original question. – Mike Stratton Apr 22 '15 at 6:23
  • I did. I wrote "under" not "in". – Sirudol Apr 22 '15 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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