Excel macro -Split semicolon separated entries to new rows

[AT BABU]

I currently have this data in a sheet

Sl No	Date	Reviewed by	Assignment	Pole Number	Feature Class	Attribute	Error Category	Observation
1	8/2/2019	Bob.B	Batch1	101039656	Cross-Arm	Rotation	Attribute	Arm is reversed, causing primaries to cross; Delete riser.
2	8/2/2019	Bob.B	Batch1	101039656	Cross-Arm	Rotation	Attribute	Arm is reversed, causing primaries to cross; Delete riser.

What I want to do is split the semicolon separated entries in the third column and insert in new rows like below:

Sl No	Date	Reviewed by	Assignment	Pole Number	Feature Class	Attribute	Error Category	Observation
1	8/2/2019	Bob.B	Batch1	101039656	Cross-Arm	Rotation	Attribute	Arm is reversed, causing primaries to cross;
2	8/2/2019	Bob.B	Batch1	101039656	Cross-Arm	Rotation	Attribute	Delete riser.
3	8/2/2019	Bob.B	Batch1	101039656	Cross-Arm	Rotation	Attribute	Arm is reversed, causing primaries to cross;
4	8/2/2019	Bob.B	Batch1	101039656	Cross-Arm	Rotation	Attribute	Delete riser.

 

[Fluff]

How about

Sub atbabu()
    Dim i As Long, j As Long
    Dim Sp As Variant
    
    For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        Sp = Split(Cells(i, 9), "; ")
        If UBound(Sp) > 0 Then
            Rows(i + 1).Insert
            Rows(i).Resize(2).FillDown
            For j = 0 To UBound(Sp)
                Cells(i + j, 9) = Sp(j)
            Next j
        End If
    Next i
End Sub

 

[Peter_SSs]

Just an option: Instead of looping through Sp and entering the values individually, you could enter them all at once.

Also, I assume that there could be more than 2 values separated by semicolons in column I so the number of rows inserted & the resize would need to be variable.

Sub atbabu_v2()
    Dim i As Long, j As Long, rws As Long
    Dim Sp As Variant
    
    For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        Sp = Split(Cells(i, 9), "; ")
        rws = UBound(Sp) + 1
        If rws > 0 Then
            Rows(i + 1).Resize(rws - 1).Insert
            Rows(i).Resize(rws).FillDown
            Cells(i, 9).Resize(rws).Value = Application.Transpose(Sp)
        End If
    Next i
End Sub

 

[Fluff]

I assume that there could be more than 2 values separated by semicolons in column I so the number of rows inserted & the resize would need to be variable.

Not enough coffee yet

 

[Peter_SSs]

Not enough coffee yet

Obviously See if this helps.

 

[AT BABU]

Hi Peter

Thank you for your support

when I run the code i get error

Just an option: Instead of looping through Sp and entering the values individually, you could enter them all at once.
Yes

original data like that

 

[AT BABU]

Thank you Fluff ; it's working

 

[Peter_SSs]

when I run the code i get error

My problem is not coffee, but sleep - it is late here
Try this instead in the line above that If rws > 1 Then

 

[Peter_SSs]

Thank you Fluff ; it's working

Does that mean you never have more than 2 items in that last column?

 

[Fluff]

Obviously See if this helps.

Much better thanks

@AT BABU
As Peter has pointed out, my code will only work correctly if you have a maximum of 2 items in col I