VBA to separate data in column using comma deliminator then put into new column

[TK2019]

I some data in lets say column D that has information such as:


Product 1, Product 2, Product 3
Product 4
Product 2, Product 4
Product 7
etc.

What i want to do is use a vba to turn this into one column somewhere else say sheet 2 column A

so: the data would look like
Product 1
Product 2
Product 3
Product 4
Product 2
Product 4
Product 7
etc


if a cell has multiple products, they have comma delimiters otherwise it's just the product by itself.

Can anyone help?

 

[Rick Rothstein]

Assuming your final list will have less than 65,500+ product names, here is a macro that you can use...

Sub ExpandCommaDelimitedData()
  Dim Arr As Variant
  Arr = Split(Join(Application.Transpose(Range("D1", Cells(Rows.Count, "D").End(xlUp))), ", "), ", ")
  Sheets("Sheet2").Range("A1").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub

 

[TK2019]

perfect. Thank you.