Inserting Rows Automatically... ?

Aitch

Board Regular
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 ?
 

lrobbo314

Well-known Member
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
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
 

Aitch

Board Regular
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!
 

Fluff

MrExcel MVP, Moderator
I have never used Google sheets, so have no idea whether it can be done.
 

Aitch

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

ABCDEFGHIJKLMNOPQ
namenameNumberetcetcetcetcetcetcetcetcetcetcetcetcetcetc
JohnSmith1
JimBrown2
JackJones3
TomAdams1
DaveBrown2
HarryCohen3
BobDavis4
RobEllis5
etcetc1
2
3
4
5
6

<tbody>
</tbody>

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

ABCDEFGHIJKLMNOPQ
namenameNUMBERetcetcetcetcetcetcetcetcetcetcetcetcetcetc
JohnSmith1
JimBrown2
JackJones3
TomAdams1
DaveBrown2
HarryCohen3
BobDavis4
RobEllis5

<tbody>
</tbody>

I hope I'm explaining okay! Thank you so much for helping!!
 
Last edited:

lrobbo314

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

Code:
function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  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++;
      }
    }
}
 
Last edited:

lrobbo314

Well-known Member
And this Google Sheets Code will insert the rows in your last post.

Code:
function InsertRowsFX() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  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());
    }
  }
}
 

Aitch

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

Code:
function InsertRowsFX() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  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?
 

lrobbo314

Well-known Member
Seems like you're doing it correctly. Can you post more sample data so I can try to see what is happening?
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top