Macro Help

[Brianfree]

Hi i have an excel document which has 1000s of rows which i need to separate the data i.e..

Row1      Row2                    Row 3
1234GHT   ASR-009898, SFR-0F9898  13
1234GHT   A09898, 9898, KJ88876   98

i need a macro to find any data in row2 that has a comma in it and add it to a new line so the result will look like..

Row1      Row2                    Row 3
1234GHT   ASR-009898              13
1234GHT   SFR-0F9898              13
1234GHT   A09898                  98
1234GHT   9898                    98
1234GHT   KJ88876                 98

Please can anyone help!

Kindest thanks,

BF

 

[FaneDuru]

If Row/Column 1 = A:A, Column 2 = B:B and Column 3 = C:C in active sheet, you can use the code:

Sub Split_Coma_delimited()
Dim i As Long, P As Worksheet, x As Long, M As Variant, E As Variant, z As Long
Set P = ActiveWorkbook.Sheets(1): i = 2
 Do Until P.Range("B" & i) = ""
    z = 0
    On Error Resume Next
    x = Application.WorksheetFunction.Search(",", P.Range("B" & i))
    If Err.Number = 0 Then
      On Error GoTo 0
      M = Split(P.Range("B" & i), ", ")
        For Each E In M
          P.Range("D" & i + z) = P.Range("A" & i)
          P.Range("E" & i + z) = E: P.Range("F" & i + z) = P.Range("C" & i)
          If z < UBound(M) Then P.Rows(i + 1 + z).Insert Shift:=xlDown
          z = z + 1
        Next E
        i = i + z - 1
    End If
    i = i + 1
 Loop
End Sub

Fane Duru