[Solved] Splitting multiple columns data into rows

by


Try this code (comments in code):

Sub Expand()
    Dim currentRow As Long, lastRow As Long, table As Variant, i As Long, _
        valuesInOneRowCol1 As Variant, valuesInOneRowCol2 As Variant, valuesInOneRowCol3 As Variant
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    currentRow = 2
    'read hwole range to memory and clear the range to fill it with expanded data
    table = Range("A2:D" & lastRow).Value2
    Range("A2:D" & lastRow).Clear
    For i = 1 To lastRow - 1
        'split comma separated lists in each column
        valuesInOneRowCol1 = Split(table(i, 2), ",")
        valuesInOneRowCol2 = Split(table(i, 3), ",")
        valuesInOneRowCol3 = Split(table(i, 4), ",")
        'write all data from one row
        For j = LBound(valuesInOneRowCol1) To UBound(valuesInOneRowCol1)
            Cells(currentRow, 1) = table(i, 1)
            Cells(currentRow, 2) = valuesInOneRowCol1(j)
            Cells(currentRow, 3) = valuesInOneRowCol2(j)
            Cells(currentRow, 4) = valuesInOneRowCol3(j)
            currentRow = currentRow + 1
        Next
    Next
End Sub

0

solved Splitting multiple columns data into rows