title: Split a comma separated list and copy the rest of the info into rows below
body: <p>I have a spreadsheet with 19940 rows and 37 columns. In the email column there are cells that have 1 email address, cells that have 0 email addresses, and cells that have multiple email addresses separated by a comma. I need to take the cells with multiple email addresses and split them up into a new row with the rest of the data duplicating into that row. Here is an <a href="https://i.stack.imgur.com/MJlHM.png" rel="nofollow noreferrer">example</a>.</p>
  <p>I found a similar post to mine, however trying the code in the answer made Excel stop responding. I've tried it multiple times and every time it ends up not responding. The code I'm using is:</p>
  <pre><code>Sub SplitPartsRows()
  Dim rng As Range
  Dim r As Long
  Dim arrParts() As String
  Dim partNum As Long
  '## For the actual file i use columns A:AK, and column AC contains the Corresponding Parts ##
  
  Set rng = Range(&quot;A1:AK19940&quot;) '## Modify as needed ##'
  
  r = 2
  Do While r &lt;= rng.Rows.Count
      '## Split the value in column AC (29) by commas, store in array ##
      arrParts = Split(rng(r, 29).Value, &quot;,&quot;)
      '## If there's more than one item in the array, add new lines ##
      If UBound(arrParts) &gt;= 1 Then '## corrected this logic for base 0 array
          rng(r, 29).Value = arrParts(0)
  
          '## Iterate over the items in the array ##
          For partNum = 1 To UBound(arrParts)
              '## Insert a new row ##'
              '## increment the row counter variable ##
              r = r + 1
              rng.Rows(r).Insert Shift:=xlDown
  
              '## Copy the row above ##'
              rng.Rows(r).Value = rng.Rows(r - 1).Value
  
              '## update the part number in the new row ##'
              rng(r, 29).Value = Trim(arrParts(partNum))
  
              '## resize our range variable as needed ##
              Set rng = rng.Resize(rng.Rows.Count + 1, rng.Columns.Count)
  
          Next
  
      End If
  '## increment the row counter variable ##
  r = r + 1
  Loop
  
  End Sub
  </code></pre>