Search code examples
excelvbainsert

Insert multiple rows based on certain conditions

Can you please help with a VBA solution on how to increment rows(output) based on certain existing rows(input)

I'd like to insert multiple rows based on "No of rows" column and for each row to concatenate certain value cell with incremental value. Also for dates, I'd like to increment with 1 day for each new row. Is there a way to do it in VBA based on that specific pattern? thank you!

enter image description here

Solution

  • Transform Data: Insert Rows

    Before

    enter image description here

    After

    enter image description here

    Almost

    Sub TransformData()
    
    ' Define constants.
    
    Const SRC_NAME As String = "Sheet1"
    Const DST_NAME As String = "Sheet2"
    
    ' Reference the workbook.
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Write the values from the source range to the source array.
    
    Dim sws As Worksheet: Set sws = wb.Sheets(SRC_NAME)
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion
    Dim srCount As Long: srCount = srg.Rows.Count
    Dim cCount As Long: cCount = srg.Columns.Count
    Dim sData(): sData = srg.Value
    
    ' Return the processed values from the source array
    ' in the destination array.
    
    ' Calculate rows.
    Dim arCount As Long
    arCount = Application.Sum(srg.Columns(cCount).Resize(srCount - 1).Offset(1))
    Dim drCount As Long: drCount = srCount + arCount
    
    ' Define array.
    Dim dData(): ReDim dData(1 To drCount, 1 To cCount)
    Dim dr As Long: dr = 1
    
    Dim sr As Long, ar As Long, c As Long
    
    ' Write headers.
    For c = 1 To cCount
        dData(1, c) = sData(1, c)
    Next c
    
    ' Write data.
    For sr = 2 To srCount
        ar = sData(sr, cCount)
        dr = dr + 1
        For c = 1 To cCount
            dData(dr, c) = sData(sr, c)
        Next c
        If ar > 0 Then
            For ar = 1 To ar
                dr = dr + 1
                dData(dr, 1) = sData(sr, 1) & " V" & ar
                dData(dr, 3) = dData(dr - 1, 3)
                dData(dr, 4) = dData(dr - 1, 4) + 1
                dData(dr, 5) = dData(dr - 1, 5) + 1
                dData(dr, 8) = "V" & ar
                dData(dr, 9) = dData(dr - 1, 9)
            Next ar
        End If
    Next sr
    
    ' Write the values from the destination array to the destination range.
    
    Dim dws As Worksheet: Set dws = wb.Sheets(DST_NAME)
    Dim drg As Range: Set drg = dws.Range("A1").Resize(drCount, cCount)
    
    dws.UsedRange.Clear
    drg.Value = dData
    
    drg.Rows(1).Font.Bold = True
    drg.Columns.AutoFit
    
    MsgBox "Data transformed.", vbInformation
    
End Sub