Need help to Transpose column into unique rows with comma delimited values

[kdevine321]

Hi,
I am trying to execute a script I copied from this site to transpose a column of values into unique rows. There was a very similar thread to my question, but the code does not work for my situation as I am a newbie to VBA. The referenced thread was http://www.mrexcel.com/forum/excel-...ranspose-multiple-comma-separated-values.html

I would like to comma delimit column "D" into unique rows per value while maintaining the relationship with the data in the other columns. Here is the example of my data:

[TABLE="width: 500"]
<tbody></tbody>[/TABLE]
[TABLE="width: 100"]
<tbody>[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD="width: 137"]GA-AG-00010-A-2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Apache Hunting Club[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]709[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl19, width: 179"]36[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD="width: 137"]GA-AG-00020-A-2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Tiger Branch Hunt Club[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]1596[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl19, width: 179"]71, 72[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD="width: 137"]GA-AG-00030-A-2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Big "O" Hunting Club[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]3058[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD="class: xl19, width: 179"]59, 64, 65, 75, 79, 84[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to make each value in Column D a unique row and still be associated with Column A-C. When I run this code I get a Runtime script error 9.

Sub SplitKeywords()
Dim MyArr, v As Long, i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row


For i = LR To 2 Step -1
MyArr = Split(Range("D" & i), ", ")
Range("D" & i) = MyArr(0)
For v = 1 To UBound(MyArr)
Rows(i + v).Insert xlShiftDown
Range("A" & i + v, "C" & i + v) = Range("A" & i, "C" & i).Value
Range("D" & i + v) = MyArr(v)
Next v
Next i


Application.ScreenUpdating = True
End Sub

Any ideas on how to correct this? My actual data spans from column A-Q and can place the "split values" in column Q.

Thanks!

 

[kdevine321]

Awesome! Thanks Hiker95! I'm actually working in Excel 2003 but it ran fine. Happy trails!

 

[hiker95]

kdevine321,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.