title: Excel VBA - Comma Delimited Cells to Rows
body: <p>Looking for VBA code to convert a dynamic table which contains one column of comma separated values into a table with no comma separated values. Columns have titles, and named ranges can be used to identify the table and columns. There could be any number of rows of these values in "Given Data". So in this example there are 4 rows of data, but in practice the data can range from 1 to over 300 rows of data.</p>
  
  <p>Given data ("Sheet1"):</p>
  
  <pre><code>A                   B       C      D
  CPN:                MPN:    Price: Text:
  CPN1, CPN2, CPN3    MPN1    1.25   Example1
  CPN4, CPN6          MPN5    3.50   Example2
  CPN7                MPN4    4.20   Example3
  CPN8, CPN9          MPN2    2.34   Example4
  </code></pre>
  
  <p>The result I need is a table on another sheet, lets just say "Sheet2", with rows for each comma separated value in "A" with the corresponding data from the original sheet without deleting the data from the first sheet.</p>
  
  <p>Needed Result ("Sheet2"):</p>
  
  <pre><code>A     B     C      D
  CPN:  MPN:  Price: Text:
  CPN1  MPN1  1.25   Example1
  CPN2  MPN1  1.25   Example1
  CPN3  MPN1  1.25   Example1
  CPN4  MPN5  3.50   Example2
  CPN6  MPN5  3.50   Example2
  CPN7  MPN4  4.20   Example3
  CPN8  MPN2  2.34   Example4
  CPN9  MPN2  2.34   Example4
  </code></pre>
  
  <p>I have tried modifying the code below from <a href="https://stackoverflow.com/questions/28678313/excel-vba-comma-separated-cells-to-rows">Here</a> but was not able to get it to handle my value types. Any help would be greatly appreciated.</p>
  
  <pre><code>Private Type data
     col1 As Integer
     col2 As String
     col3 As String
  End Type
  
  Sub SplitAndCopy()
  
     Dim x%, y%, c%
     Dim arrData() As data
     Dim splitCol() As String
  
     ReDim arrData(1 To Cells(1, 1).End(xlDown))
  
     x = 1: y = 1: c = 1
  
     Do Until Cells(x, 1) = ""
         arrData(x).col1 = Cells(x, 1)
         arrData(x).col2 = Cells(x, 2)
         arrData(x).col3 = Cells(x, 3)
  
         x = x + 1
      Loop
  
      [a:d].Clear
  
      For x = 1 To UBound(arrData)
  
          Cells(c, 2) = arrData(x).col2
          splitCol = Split(Mid(arrData(x).col3, 2, Len(arrData(x).col3) - 2), ",")
  
          ' sort splitCol
  
          For y = 0 To UBound(splitCol)
              Cells(c, 1) = arrData(x).col1
              Cells(c, 3) = splitCol(y)
              c = c + 1
          Next y
  
      Next x
  
  End Sub
  </code></pre>