Text to Rows with copy

[CTrott85]

Good day to you all, and thank you in advance.

I am running Excel 2003, having severe difficulty performing what feels like it should be a very simple macro. I have cells from Column A through M, and the text in column A has multiple data points of various character lengths from 2 to 8, delimited by a comma.

Example:
A B C D
C2, R4, D5 Cd0456 ToysRBoys 52.49
B354, D92 AlphaBeta MoreMore 0.34

What i would like to accomplish is a seperation of the comma delimited data in column a, and a copy to the following rows of every other column.

Example:
A B C D
C2 Cd0456 ToysRBoys 52.49
R4 Cd0456 ToysRBoys 52.49
D5 Cd0456 ToysRBoys 52.49
B354 AlphaBeta MoreMore 0.34
D92 AlphaBeta MoreMore 0.34

Everything I've found has been rather application specific to their charts, and frankly I don't have the programming expertise to figure out how to modify the code.

Can someone point me in the right direction please? Again thank you in advance.

 

[Marcelo Branco]

Hi and welcome

Assuming
Original data in Sheet1 beginning in row 2, columns A B C D
Destination sheet = Sheet2
Data begginning in A2

Maybe this

Sub arrData()
    Dim lastRow As Long, i As Long, j As Long, lin As Long
    Dim v As Variant
    Dim wkOrigin As Worksheet, wkDest As Worksheet
    
    Set wkOrigin = Sheets("Sheet1") '<-- Adjust if neede
    Set wkDest = Sheets("Sheet2") '<-- Adjust if needed
    
    lin = 1
    With wkOrigin
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        For i = 2 To lastRow
            v = Split(.Cells(i, 1), ",")
            
            For j = 0 To UBound(v)
                lin = lin + 1
                wkDest.Cells(lin, 1) = v(j)
                wkDest.Cells(lin, 2) = .Cells(i, 2)
                wkDest.Cells(lin, 3) = .Cells(i, 3)
                wkDest.Cells(lin, 4) = .Cells(i, 4)
            Next j
            
        Next i
    End With
    
End Sub

M.

 

[CTrott85]

Your code worked like a charm Marcelo.

Thank you so much.

 

[Marcelo Branco]

Your code worked like a charm Marcelo.

Thank you so much.

You are very welcome and tks for the feedback.

M,