Sub CsvSplit()
     
    Dim DataItem As Variant
    Dim lngMyOffset As Long
    Dim StartRowNo As Long
    Dim EndRowNo As Long
    Dim StartColumn As String
    Dim rngCell As Range
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    StartRowNo = 5 'Starting row number for the data. Change to suit.
    StartColumn = "A" 'Column containing the data. Change to suit.
     
    Application.ScreenUpdating = False
    
    ws.Select
     
    For Each rngCell In Range(StartColumn & StartRowNo & ":" & StartColumn & Cells(Rows.Count, StartColumn).End(xlUp).Row)
         
        lngMyOffset = 0
         
        For Each DataItem In Split(rngCell.Value, ",")
            If lngMyOffset = 0 Then
                rngCell.Offset(0, 4).Value = DataItem
            ElseIf lngMyOffset = 2 Then
                rngCell.Offset(0, 5).Value = DataItem
            ElseIf ingMyOffset = 0 Then
                rngCell.Offset(0, 7).Value = DataItem
            End If
            lngMyOffset = lngMyOffset + 2
        Next DataItem
         
    Next rngCell
       
    'Rows("1:3").Insert
     
    Application.ScreenUpdating = True
     
    MsgBox "Data split by comma now completed"
     
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *