Inserting Rows Automatically... ?

Aitch

Board Regular
Joined
Jan 27, 2019
Messages
119
Office Version
  1. 2010
Platform
  1. Windows
I'm working with a long list which is a bunch of names in one column - they are in teams of a maximum of 5, but usually less so I need to create blanks rows to keep things in order:

Column A/B is what I need to achieve - from the basic list I get in column E...

ABCDE
TeamA1JackJack
2JillJill
3JohnJohn
4JaneJane
5
Tom
TeamB1TomHarry
2Harry
3Bill
4Phil
5Bob
Rob
TeamC1Bill
2Phil
3Bob
4Rob
5

<tbody>
</tbody>



Is there any way to Insert (Blank) Rows automatically to the list I get in column E ?
 
Seems like you're doing it correctly. Can you post more sample data so I can try to see what is happening?

I'll try - hopefully screenshots will make it clearer... also, I said 25 rows in total before - sorry, it should be 26!



This is the list I receive - there are about 100 "groups" of between 1 to 25 all in a long list... column C is how they are numbered etc

Before.jpg




And this is the format it needs to be in - all the groups separated consistently with 25 rows, plus 1 extra row marked in orange etc... so there is always 25 rows between each orange row

After.jpg




When I run your code - it seems to work on the first group fine, but then carries on inserting rows afterwards, so pushing the rest of the data continuously down...


Thank you again!
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Of course. Easy fix. Just move from bottom to top instead of top to bottom. Here's the updated code. Worked on bigger data set over here.

Code:
function InsertRowsFX() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  for(var i=lastRow; i >=3; i--) {
    if(sheet.getRange(i, 3).getValue()==1) {
      sheet.insertRowsBefore(i, 26 - sheet.getRange(i, 3).getValue());
    }
  }
}
 
Upvote 0
Awesome! Thank you so much!

But there's another problem now lol - this error message keeps coming up ... "Exceeded maximum execution time"

Is there any way to increase the execution time or speed up the script?

Or can I re-run the script somehow so it carries on from where it left off?
 
Last edited:
Upvote 0
Of course. Easy fix. Just move from bottom to top instead of top to bottom. Here's the updated code. Worked on bigger data set over here.

Code:
function InsertRowsFX() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  for(var i=lastRow; i >=3; i--) {
    if(sheet.getRange(i, 3).getValue()==1) {
      sheet.insertRowsBefore(i, 26 - sheet.getRange(i, 3).getValue());
    }
  }
}

Is there no fix or work-around for this?



Awesome! Thank you so much!

But there's another problem now lol - this error message keeps coming up ... "Exceeded maximum execution time"

Is there any way to increase the execution time or speed up the script?

Or can I re-run the script somehow so it carries on from where it left off?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top