Announcement

Collapse
No announcement yet.

Inserting X number of rows and copying data down loop

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Inserting X number of rows and copying data down loop



    Hello everyone - junior programmer wanna be here who is ready for a Xanax. I am trying to insert X number of rows below each visible row (X = Total Count column of that row). I want to copy the original row into the X number of rows and then repeat for all remaining rows. Any help would be greatly appreciated!!

    Issuer Facility LoanX ID Price Depth Total Count
    Issuer A Term Loan B LXAAAA1 100.771 LoanX 3 2
    Issuer B 2017 Class B Term Loan LXAAAA2 100.216 LoanX 9 2
    Issuer C Term Loan B LXAAAA3 100.625 LoanX 2 3
    Issuer D Term Loan B-1 LXAAAA4 100.797 LoanX 4 4
    Issuer E New Term Loan B LXAAAA5 100.95 LoanX 5 6


    Sub Copy_Rows_Down()

    Dim sh As Worksheet
    Set sh = Worksheets("Sheet1")
    Dim LastRow As Long
    LastRow = Range("AA" & Rows.count).End(xlUp).Row

    Dim totalTRADES As Range
    Set totalTRADES = ActiveCell.Offset(0, 6).Select

    Dim i As Long, j As Long

    i = 3
    Do While i <= LastRow

    For j = 1 To totalTRADES
    sh.Rows(i).Copy
    sh.Rows(i).Offset(1).Insert Shift:=xlDown
    Next j

    i = i + totalTRADES + 1
    LastRow = LastRow + totalDS

    Loop
    Last edited by dsohng; 4 days ago.

  • #2
    If you are inserting rows, the best way is to work from the last row up, something like this:

    Code:
    Sub InsertRows()
      Dim wsSht As Worksheet
      Dim j As Long
      
      Set wsSht = ThisWorkbook.Sheets(1)
      
      With wsSht
        'insert rows in reverse order starting from last row as determined from column 1 (A)
        For j = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1
          'insert the number of rows as specified in column 6 (F)
          .Rows(j + 1 & ":" & j + .Cells(j, 6).Value).Insert Shift:=xlDown
        Next
      End With
    End Sub

    Comment


    • #3
      Thanks for responding Gijsmo. When I run the code nothing happens. When I step thru, after running
      For j = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1 it skips the next line of code and goes directly to "End with". any suggestions? Thanks.

      Comment


      • #4


        It sounds like it is calculating a different last row to what is on your sheet. The code sample assumed your data started in column A as no sample worksheet was provided.

        Looking at your code again, it seems your data may start at column AA instead of A, so you would need to adjust my code accordingly.

        The version below will make it easier to adjust the starting column, just change the value of the StartCol constant.

        Code:
        Sub InsertRows()
          Const StartCol = 1 'change the starting column value here  eg 1 = A or 27 = AA
          Const Offset = 5   'number of columns after StartCol where Total Count is found
          Dim wsSht As Worksheet
          Dim j As Long
          
          Set wsSht = ThisWorkbook.Sheets(1)
          
          With wsSht
            'insert rows in reverse order starting from last row as determined from StartCol
            For j = .Cells(.Rows.Count, StartCol).End(xlUp).Row To 2 Step -1
              'insert the number of as specified in column 6 (F)
              .Rows(j + 1 & ":" & j + .Cells(j, StartCol + Offset).Value).Insert Shift:=xlDown
            Next
          End With
        End Sub

        Comment

        Working...
        X