Inserting Rows Automatically... ?
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Inserting Rows Automatically... ?

  1. #1
    Board Regular
    Join Date
    Jan 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 ?

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    Last edited by lrobbo314; Jun 21st, 2019 at 10:09 PM.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,051
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default 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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Jan 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,051
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Inserting Rows Automatically... ?

    I have never used Google sheets, so have no idea whether it can be done.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Jan 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!!
    Last edited by Aitch; Aug 20th, 2019 at 07:43 PM.

  7. #7
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default 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 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 by lrobbo314; Aug 20th, 2019 at 09:34 PM.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  8. #8
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Inserting Rows Automatically... ?

    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());
        }
      }
    }
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  9. #9
    Board Regular
    Join Date
    Jan 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inserting Rows Automatically... ?

    Quote Originally Posted by lrobbo314 View Post
    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?

  10. #10
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default 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?
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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