Insert new row based on character within a cell

[bocrad75]

Hello,

I'm wondering if someone can assist me with this. I am looking for VBA code to insert a new row and copy down based on seeing a character after a semi-colon.

For example:

Current:

ID1 Hi;Hello;What;
ID2 There;This;That;
ID3 Wahoo;


Desired:

ID1 Hi;
ID1 Hello;
ID1 What;
ID2 There;
ID2 This;
ID2 That;
ID3 Wahoo;

I need column A to copy down to maintain the key and column B to insert and parse out only if there is a character after a semi-colon.

Any help would be greatly appreciated.

Thank you,
DB

[jindon]

try

Sub test()
    Dim i As Long, ii As Long, temp, RegX As Object, m As Object
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Global = True
        .Pattern = "[^;]+;"
    End With
    With Cells(1).CurrentRegion
        For i = .Rows.Count To 1 Step -1
            temp = .Cells(i, 2).Value & ";"
            Set m = RegX.Execute(temp)
            If m.Count > 1 Then
                For ii = m.Count - 1 To 0 Step -1
                    If ii > 0 Then
                        .Rows(i + 1).Insert xlShiftDown
                        .Rows(i + 1).Value = Array(.Cells(i, 1).Value, m(ii))
                    Else
                        .Rows(i).Value = Array(.Cells(i, 1).Value, m(ii))
                    End If
                Next
            End If
        Next
    End With
End Sub

[bocrad75]

Hi Jindon,

This works! I really appreciate the assistance.

Thanks again,
DB