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 ?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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:
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
I have never used Google sheets, so have no idea whether it can be done.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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());
    }
  }
}
 
Upvote 0
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?
 
Upvote 0
Seems like you're doing it correctly. Can you post more sample data so I can try to see what is happening?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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