Thread: Inserting Rows Automatically... ? Thanks:  6 Post #5302487 (1)Post #5329012 (1)Post #5298369 (1)Post #5329019 (1)Post #5298512 (1) Likes:  2 Post #5329621 (1)Post #5330940 (1)

1. Inserting Rows Automatically... ?

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...

 A B C D E TeamA 1 Jack Jack 2 Jill Jill 3 John John 4 Jane Jane 5 Tom TeamB 1 Tom Harry 2 Harry 3 Bill 4 Phil 5 Bob Rob TeamC 1 Bill 2 Phil 3 Bob 4 Rob 5

Is there any way to Insert (Blank) Rows automatically to the list I get in column E ?  Reply With Quote

2. Re: Inserting Rows Automatically... ?

So, if I understand correctly, if your original list is in column E, then this code will replicate what you have in columns A:C.

Code:
Sub Convert()
Dim AR() As Variant: AR = Range("E1:E" & Range("E" & Rows.Count).End(xlUp).Row).Value
Dim ro As Integer: ro = 1
Dim cnt As Integer: cnt = 1
Dim tChar As Integer: tChar = 65
Dim b As Boolean: b = False

For i = LBound(AR) To UBound(AR)
If cnt = 1 Then Cells(ro, 1) = "Team" & Chr(tChar)
If cnt < 6 Then Cells(ro, 2) = cnt
If AR(i, 1) = vbNullString Then
For j = cnt To 5
Cells(ro, 2).Value = cnt
cnt = cnt + 1
ro = ro + 1
Next j
Else
Cells(ro, 3) = AR(i, 1)
End If
If cnt > 5 Then
cnt = 0
tChar = tChar + 1
End If
ro = ro + 1
cnt = cnt + 1
Next i

If cnt < 6 Then
For k = cnt To 5
Cells(ro, 2).Value = cnt
cnt = cnt + 1
ro = ro + 1
Next k
End If

End Sub  Reply With Quote

3. Re: Inserting Rows Automatically... ?

Another option
Code:
Sub Aitch()
Dim i As Long, j As Long
Dim Ar As Areas
Dim Ary As Variant

Ary = Application.Transpose(Array(1, 2, 3, 4, 5))
Set Ar = Range("E:E").SpecialCells(xlConstants).Areas
j = 2
For i = 1 To Ar.Count
Range("A" & j).Value = "Team" & Chr(i + 64)
Range("B" & j).Resize(5).Value = Ary
Range("C" & j).Resize(Ar(i).Count).Value = Ar(i).Value
j = j + 6
Next i
End Sub  Reply With Quote

4. Re: Inserting Rows Automatically... ?

Dangit! I was hoping it wouldn't involve code as I've never used this method before! How do I proceed?

I receive the list from someone using Google Sheets - can I get Sheets to perform this action so I don't need to transfer the data to Excel?

Thank you so much for taking the time to write all that code for little ole me - you guys rock!  Reply With Quote

5. Re: Inserting Rows Automatically... ?

I have never used Google sheets, so have no idea whether it can be done.  Reply With Quote

6. Re: Inserting Rows Automatically... ?

Could this code be amended to work with another similar sheet?

It has columns A to Q - with column C being differing numbers from 1 to 25 containing a set of data...

 A B C D E F G H I J K L M N O P Q name name Number etc etc etc etc etc etc etc etc etc etc etc etc etc etc John Smith 1 Jim Brown 2 Jack Jones 3 Tom Adams 1 Dave Brown 2 Harry Cohen 3 Bob Davis 4 Rob Ellis 5 etc etc 1 2 3 4 5 6

Can it automatically insert rows so that there is always 25 rows in total between each new set...

Like in the example above - the first set contains 3 rows of data numbered by column C... so it would insert 22 empty rows underneath...

Then doing the same with the next set of 5 by inserting 20 rows etc etc... so looking something like this below

 A B C D E F G H I J K L M N O P Q name name NUMBER etc etc etc etc etc etc etc etc etc etc etc etc etc etc John Smith 1 Jim Brown 2 Jack Jones 3 Tom Adams 1 Dave Brown 2 Harry Cohen 3 Bob Davis 4 Rob Ellis 5

I hope I'm explaining okay! Thank you so much for helping!!  Reply With Quote

7. Re: Inserting Rows Automatically... ?

Not sure about your last post yet, but here is a Google Sheet version of my first code.

Code:
function myFunction() {
var lastRow = sheet.getLastRow();
var arr = sheet.getRange("E1:E" + lastRow).getValues();
var cnt = 1;
var ro = 1;
var tChar = 65;

for (var i = 0; i < arr.length; i++) {
if(cnt==1) {
sheet.getRange(ro, 1).setValue('Team' + String.fromCharCode(tChar));
}
if(cnt < 6) {
sheet.getRange(ro, 2).setValue(cnt);
}
if(arr[i] == '') {
for(var j = cnt; j < 6; j++) {
sheet.getRange(ro, 2).setValue(cnt);
cnt++;
ro++;
}
} else {
sheet.getRange(ro,3).setValue(arr[i]);
}
if(cnt >5) {
cnt = 0;
tChar ++;
}
ro++;
cnt++;
}
if(cnt<6) {
for(var k = cnt; k < 6; k++) {
sheet.getRange(ro,2).setValue(cnt);
cnt++;
ro++;
}
}
}  Reply With Quote

8. Re: Inserting Rows Automatically... ?

And this Google Sheets Code will insert the rows in your last post.

Code:
function InsertRowsFX() {
var lastRow = sheet.getLastRow();

for(var i=3; i < lastRow; i++) {
if(sheet.getRange(i, 3).getValue()==1) {
sheet.insertRowsAfter(i-1, 25 - sheet.getRange(i-1, 3).getValue());
}
}
}  Reply With Quote

9. Re: Inserting Rows Automatically... ? Originally Posted by lrobbo314 And this Google Sheets Code will insert the rows in your last post.

Code:
function InsertRowsFX() {
var lastRow = sheet.getLastRow();

for(var i=3; i < lastRow; i++) {
if(sheet.getRange(i, 3).getValue()==1) {
sheet.insertRowsAfter(i-1, 25 - sheet.getRange(i-1, 3).getValue());
}
}
}
You are Awesome bro!

Is this how I should proceed...
1) I copy your code in to Google Sheets menu - Tools>Script Editor... which opens a new sheet
2) Then go back to my working sheet - and click the corner to select all the cells
3) Then go to the Script sheet again and click Run

I must be doing something wrong as the code works fine on the first set - but on all the rest, it keeps inserting rows over and over again, underneath the first?  Reply With Quote

10. Re: Inserting Rows Automatically... ?

Seems like you're doing it correctly. Can you post more sample data so I can try to see what is happening?  Reply With Quote

User Tag List

Tags for this Thread

automatically, column, jack, list, rows  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•