read complete row insert as new row

[Danny54]

i've been playing with this all morning but no progress. Any suggestions would be helpful.


I'm reading in a xls file looking at column I. When I find multiple entries in col "I" separated by a comma, i need to create a new row for each Item I find and insert it into a new row. Right now I have the values in a array but for the life of me, im having trouble from there.

Thanks


the data file would be as such

a b c d e f g h i
1 2 3 4 5 6 7 8 Red
1 2 3 4 5 6 7 8 Blue,
Green,
Yellow
1 2 3 4 5 6 7 8 Brown


The output
a b c d e f g h i
1 2 3 4 5 6 7 8 Red
1 2 3 4 5 6 7 8 Blue
1 2 3 4 5 6 7 8 Green
1 2 3 4 5 6 7 8 Yellow
1 2 3 4 5 6 7 8 Brown



'split ip columns
Dim FullIP As Variant
Dim txt As String


N = Cells(Rows.Count, "A").End(xlUp).Row
For i = 8 To N
txt = Range("I" & i).Value
FullIP = Split(txt, ",")
For j = 0 To UBound(FullIP)


Next j
Next i

 

[Fluff]

How about

Sub Danny54()
    Dim FullIP As Variant
    Dim i As Long

    For i = Range("A" & Rows.Count).End(xlUp).Row To 8 Step -1
        FullIP = Split(Range("I" & i).Value, ",")
        If UBound(FullIP) > 0 Then
            Rows(i + 1).Resize(UBound(FullIP)).Insert
            Rows(i).Resize(UBound(FullIP) + 1).FillDown
            Range("I" & i).Resize(UBound(FullIP) + 1).Value = Application.Transpose(FullIP)
        End If
    Next i
End Sub

 

[Danny54]

Wow,

that worked Great! I'll spend some time with it to fully understand.

Thanks so much!

 

[Fluff]

Glad to help & thanks for the feedback