View Single Post
 
Old 02-07-2018, 01:08 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

Assuming data starts in A2, a comma is the separator and no event code for the sheet,
on a copy of your workbook, paste this into a standard module and give it a try, alter the sheet name as required
Code:
Sub Testing_1()

Dim lr As Long, i As Integer, j As Integer, ray

Application.ScreenUpdating = False

With Sheets("Sheet1")
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        ray = Split(.Cells(i, 4).Value, ",")
        If UBound(ray) > 0 Then
            .Range("A" & i).Resize(, 4).Copy
            .Range("A" & i + 1).Resize(UBound(ray), 4).Insert xlShiftDown
            For j = LBound(ray) To UBound(ray)
                .Cells(i, 4).Offset(j).Value = Trim(ray(j))
            Next j
        End If
    Next i
End With
    
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
Reply With Quote