Help: Seperate data and insert new rows

[GoldServe]

Say for example, I have the following row in excel:

A | B | C | D, E, F

I want to scan third column and insert two more rows. Effectively seperating the comma delimited list:

A | B | C | D
A | B | C | E
A | B | C | F

Can someone whip up something simple to do that? Thanks!

[shasur]

You can use the split function to do this

Here is a primitive way of doing this

[VBA]Sub ColD_Split()

Dim arTemp
Dim sColumnAVal As String
Dim sColumnBVal As String
Dim sColumnCVal As String
Dim sColumnDVal As String

sColumnAVal = Cells(1, 1).Value
sColumnBVal = Cells(1, 2).Value
sColumnCVal = Cells(1, 3).Value
sColumnDVal = Cells(1, 4).Value

arTemp = Split(sColumnDVal, ",")

Cells(1, 4).Value = arTemp(0)

J = 1
For i = 0 To UBound(arTemp)
J = J + 1
Cells(J, 1).Value = sColumnAVal
Cells(J, 2).Value = sColumnBVal
Cells(J, 3).Value = sColumnCVal
Cells(J, 4).Value = arTemp(i)
Next i
End Sub

[/VBA]

[Bob Phillips]

An alternative

[vba]

Public Sub Test()
Dim aryVals As Variant
Dim cRows As Long

aryVals = Split(Range("D1").Value, ",")
cRows = UBound(aryVals) - LBound(aryVals) + 1
Range("D1").Resize(cRows) = Application.Transpose(aryVals)
Range("A1:C1").AutoFill Range("A1:C1").Resize(cRows)
End Sub
[/vba]