Vba code to split data with delimited "," in separate rows

Need help to get the data from 3 separate columns with delimited comma in separate rows (new sheet)
sample attached also shown below

2017-04-25_143936.jpg

It is not as per my requirement.
Data should generate in new sheet with all 3 columns (not only one column)

also it should not be only for one cell, it should be multiple cells in entire column

Assumed Column (A) Column (BW) Column (CG) are column reference.

Sub test()
    Dim a, b, e, i As Long, ii As Long, x, n As Long
    With Sheets("sheet1").Cells(1).CurrentRegion.Resize(, 85)
        a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), [{85,1,75}])
    End With
    ReDim b(1 To 10000, 1 To UBound(a, 2))
    For i = 1 To UBound(a, 1)
        If a(i, 3) <> "" Then
            For Each e In Split(a(i, 3), ",")
                n = n + 1
                For ii = 1 To UBound(a, 1)
                    b(n, ii) = a(i, ii)
                Next
                b(n, 3) = Trim$(e)
            Next
        Else
            n = n + 1
            For ii = 1 To UBound(a, 1)
                b(n, ii) = a(i, ii)
            Next
        End If
    Next
    Sheets.Add.Cells(1).Resize(n, UBound(b, 2)).Value = b
End Sub

YES, THOSE ARE COLUMN REFERENCES. BUT YOUR CODE IS NOT WORKING.
NEW SHEET IS CREATED ONLY WITH

CURRENT DATA
Column (A)
MM

Are you trying to express your feeling with ALL CPS?
Then good luck.

No No.... Actually when we type in all CAPS, forum automatically turns it into =Proper() case. But I don't know why it didn't happen this time. Please don't take it otherwise.

How would I express to the expert from whom I am expecting Help?

See the attached

Fantastic.... this is what i was looking for.

but the actual data is not available only in single row.... hence request you instead of using only one cell use entire column. because the data is available in entire columns (multiple rows)

also it might contain BLANK rows which should be ignored.

you really reached very close to my actual requirement - THANKS

The you have to upload a workbook with Correct sheet layouts and EXACT results that you want.
Otherwise it will be just wasting time for both.

Attached is sample of exact format (not required columns hidden)

Change to

Sub test()
    Dim a, b, e, i As Long, ii As Long, x, n As Long
    With Sheets("sheet1")
        With .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Resize(, 85)
            a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), [{85,1,75}])
        End With
    End With
    ReDim b(1 To 10000, 1 To UBound(a, 2))
    For i = 1 To UBound(a, 1)
        If a(i, 3) <> "" Then
            For Each e In Split(a(i, 3), ",")
                n = n + 1
                For ii = 1 To UBound(a, 2)
                    b(n, ii) = a(i, ii)
                Next
                b(n, 3) = Trim$(e)
            Next
        Else
            n = n + 1
            For ii = 1 To UBound(a, 2)
                b(n, ii) = a(i, ii)
            Next
        End If
    Next
    Sheets.Add.Cells(1).Resize(n, UBound(b, 2)).Value = b
End Sub

THANKS - IT WORKED

One Additional Request - Just now I thought about one more possibility i.e. what if other columns will also contains multiple data separated with "," comma. Can you help with that also.

This is just addition (leave it if not a valid request)

sample file attached with REQUIREMENT sheet

Also can we able to remove the blank rows?

consider this as last request

Thanks
MG

Originally Posted by gaikwad.mm@gmail.com

consider this as last request

Sub test()
    Dim a, b, e, s, v, i As Long, ii As Long, n As Long
    With Sheets("sheet1")
        With .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Resize(, 85)
            a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), [{85,1,75}])
        End With
    End With
    ReDim b(1 To 10000, 1 To UBound(a, 2))
    For i = 1 To UBound(a, 1)
        If (a(i, 1) <> "") * (a(i, 2) <> "") * (a(i, 3) <> "") Then
            For Each e In Split(a(i, 1), ",")
                For Each v In Split(a(i, 2), ",")
                    For Each s In Split(a(i, 3), ",")
                        n = n + 1
                        b(n, 1) = e: b(n, 2) = v: b(n, 3) = s
                    Next
                Next
            Next
        Else
            n = n + 1: For ii = 1 To UBound(a, 2): b(n, ii) = a(i, ii): Next
        End If
    Next
    Sheets.Add.Cells(1).Resize(n, UBound(b, 2)).Value = b
End Sub

THANKS A LOT FRIEND & yes will not disturb you more your help is really appreciated.

BUT don't know why I got "Run Time Error for Type Mismatch" in a = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), [{85,1,75}])

Most probably you have more than 255 characters in any one cell.
Try change to

Sub test()
    Dim a, b, cg, bw, e, s, v, i As Long, ii As Long, n As Long
    With Sheets("sheet1")
        With .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Resize(, 85)
            a = .Columns(1).Value
            cg = .Columns("cg").Value
            bw = .Columns("bw").Value
        End With
    End With
    ReDim b(1 To 10000, 1 To 3)
    For i = 1 To UBound(a, 1)
        If (cg(i, 1) <> "") * (a(i, 1) <> "") * (bw(i, 1) <> "") Then
            For Each e In Split(cg(i, 1), ",")
                For Each v In Split(a(i, 1), ",")
                    For Each s In Split(bw(i, 1), ",")
                        n = n + 1
                        b(n, 1) = e: b(n, 2) = v: b(n, 3) = s
                    Next
                Next
            Next
        Else
            n = n + 1: For ii = 1 To UBound(a, 2): b(n, ii) = a(i, ii): Next
        End If
    Next
    Sheets.Add.Cells(1).Resize(n, UBound(b, 2)).Value = b
End Sub