Google-sheets – Macro to move down and separate data

google sheetsgoogle-apps-scriptgoogle-sheets-macro

I wish to use a macro in Google Sheets to

find the number 1 in column A
then move that corresponding row and all following rows down to start at A9 row
then find the number 2 in column A
then move that corresponding row and all following rows down to start at A25
then find the number 3 in column A
then move that corresponding row and all following rows down to start at A41 and so on down to number 24.

Each number 1,2,3,… has a varying number of data rows below it before the next number and so I can't simply do one "paste" at A9 with all of the information being in the correct place.

I have tried recording a macro using FIND menu item but it does not work.

Best Answer

In Google Sheets the Find command highlights the cells that match the searched value. To jump (activate) a matching cell, instead use Find and Replace. Bear in mind that the macro recorder will not record any action for Find and for Find and Replace will only record the cell activation.

I suggest you that instead of the Google Sheets macro recorder write your script directly on the script editor. Once you did this, you could "Import" the script as a macro to be called from the Macros menu and / or a custom keyboard shortcut.

Code

function autoAddRows() {
  var sh = SpreadsheetApp.getActiveSheet();
  var cRow = sh.getActiveRange().getRowIndex(), mRow = sh.getMaxRows();

  if((mRow-cRow) < 1) {
    var lCol = sh.getLastColumn(), key = sh.getRange(mRow,lCol).getValue();
    sh.insertRowAfter(mRow), sh.getRange(mRow+1,lCol).setValue(key+1);
  }
}

Remarks

var ss isn't necessary, since you don't use it in the script.
you need the row of the active cell, compared to the last row (getMaxRows)
add all the goodies into the IF statement that need to be executed, when mRow-cRow=0

Example

I've prepared a file for you to experiment with: Inserting Rows + Reference

Google-sheets – Moving down the fixed row numbers

No, there isn't. That list of numbers shows you what row you are in. If you need to have a column of numbers of at the left, put your own one in, and give it a category like "Id" or "RowNumber"

Best Answer

Related Solutions

Google Sheets – Inserting Rows and Adding Cell References

Code

Remarks

Example

Google-sheets – Moving down the fixed row numbers

Related Topic