create new row if a cell has a "comma"

[Afarag]

Hello there,

please i need help as i have some data in a table, there is a column that contain a string and might have a "comma" that split an items, i need to duplicate the column that have a comma

1st table: as shown @ column ("E"), there is a Comma that split the items.
as shown at 2nd table eg: I need to separate each item at the next row till finish the loop.

 

[jindon]

Try

Sub test()
    Dim a, b, i As Long, ii As Long, e, n As Long
    a = Sheets("data").Cells(1).CurrentRegion.Value
    ReDim b(1 To 100000, 1 To UBound(a, 2))
    For i = 1 To UBound(a, 1)
        If a(i, 5) <> "" Then
            For Each e In Split(a(i, 5), ",")
                n = n + 1
                For ii = 1 To UBound(a, 2)
                    If ii = 5 Then
                        b(n, ii) = e
                    Else
                        b(n, ii) = a(i, ii)
                    End If
                Next
            Next
        End If
    Next
    With Sheets.Add.Cells(1).Resize(n, UBound(a, 2))
        .Value = b: .Columns.AutoFit
    End With
End Sub

 

[Afarag]

Hi Jindon,

thanks a lot, it works perfectly.

Gratefully,

 

[jindon]

You are welcome.

 

[jimbosi]

Try

Sub test()
    Dim a, b, i As Long, ii As Long, e, n As Long
    a = Sheets("data").Cells(1).CurrentRegion.Value
    ReDim b(1 To 100000, 1 To UBound(a, 2))
    For i = 1 To UBound(a, 1)
        If a(i, 5) <> "" Then
            For Each e In Split(a(i, 5), ",")
                n = n + 1
                For ii = 1 To UBound(a, 2)
                    If ii = 5 Then
                        b(n, ii) = e
                    Else
                        b(n, ii) = a(i, ii)
                    End If
                Next
            Next
        End If
    Next
    With Sheets.Add.Cells(1).Resize(n, UBound(a, 2))
        .Value = b: .Columns.AutoFit
    End With
End Sub

This script works great. I can't tell how it's pointed to column E. How can I modify the code to point it to a column of my choosing?

Also, I'm having a discussion on Excel Forum http://www.excelforum.com/excel-pro...text-to-columns-like-probably-vba-script.html and several users there submitted potential solutions. Ironically, yours is the one that worked.

To help those users (and others), may I either refer your post/code here at chandoo.org or submit a post on Excel Forum directing them to this script?

I wanted those users to see the logic in your script and how it worked. Let me know what you'll allow me to do.

Also, tks for providing the script. I've been stuck with this problem for a long time. Cheers!