Split values in cell D onto next line????

[LebronJames]

My Excel spreadsheet is setup like this:

Cell A Cell B Cell C Cell D
John Smith Yes Alberta,Ontario,Quebec

How can I code it so that I can turn that into this:

Cell A Cell B Cell C Cell D
John Smith Yes Alberta
John Smith Yes Ontario
John Smith Yes Quebec

Basically take the values in Cell D (where there is a comma) and it add to the next row containing the other values in Cell A, B, and C

Help is appreciated.....

Johnny

 

[PHV]

Before asking for new help, why not "closing" your other threads, signaling if the suggestion worked for you for benefit of other members having same issue as you had ?

 

[byundt]

Johnny,
Here's a sub that loops backwards through column D all the way to row 1. If a cell in column D contains one or more commas, then blank rows are inserted below that point to receive copies of the data in columns A:C as well as the data in column D split at the commas. This sub goes in a regular modules sheet.

Sub SplitAddress()
Dim X As Variant
Dim rg As Range
Dim i As Long, j As Long
Application.ScreenUpdating = False
For i = Cells(65536, 4).End(xlUp).Row To 1 Step -1
    If Cells(i, 4) <> "" Then
        X = Split(Cells(i, 4), ",")
        j = UBound(X) + 1
        If j > 1 Then
            Range(Cells(i + 1, 4), Cells(i + j - 1, 4)).EntireRow.Insert
            Set rg = Range(Cells(i, 4), Cells(i + j - 1, 4))
            rg.Value = Application.Transpose(X)
            Range(rg.Offset(0, -3), rg.Offset(0, -1)).Value = Range(Cells(i, 1), Cells(i, 3)).Value
        End If
    End If
Next i
Application.ScreenUpdating = True
End Sub

Brad