title: VBA to correctly loop through an array
body: <p>I have read through a number of summaries of arrays but I am still lost and looking for much appreciated help.  I have successfully created a non-array macro that copies a row in my ws and places below that parent row three copies.  It does this for every row in the ws.</p>
  
  <p>eg</p>
  
  <pre><code>From:
  
  ColA     ColB
  Tom      Tent
  Barry    Stove
  
  To:
  
  ColA     ColB
  Tom      Tent
  Tom      Tent
  Tom      Tent
  Tom      Tent
  Barry    Stove
  Barry    Stove
  Barry    Stove
  Barry    Stove
  </code></pre>
  
  <p>There are > 4000 rows to loop through.  My code works fine but it is slow.  So I read that placing the ws into an array is better and then loop through the array.  Here is where I am lost with arrays; how do I execute this copy and paste x 3 when I bring the ws into an array?  I have written some code below but not sure how to execute this further.  Many thanks.</p>
  
  <pre><code>Sub LoadDataintoArray()
  
  Dim StrArray As Variant
  Dim TotalRows As Long
  
  
  
  TotalRows = Rows(Rows.Count).End(xlUp).Row
  StrArray = Range(Cells(1, 1), Cells(TotalRows, 1)).Value
  
  MsgBox "Loaded " &amp; UBound(StrArray) &amp; " items!"
  
  'HERE I NOW WISH TO COPY EACH ROW IN THE WS (EXCEPT HEADER) AND PASTE THREE COPIES OF THAT ROW IMMEDIATELY BELOW THE PARENT ROW
  
  'CODE I USED NOT USNG AN ARRAY IS BELOW
  '
  '    lRow = 2
  '    Do While (Cells(lRow, "B") &lt;&gt; "")
  '
  '        RepeatFactor = 4
  '
  '        Range(Cells(lRow, "A"), Cells(lRow, "G")).Copy
  '
  '        Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "G")).Select
  '
  '        Selection.Insert Shift:=xlDown
  '
  '           lRow = lRow + RepeatFactor - 1
  '
  '        lRow = lRow + 1
  '    Loop
  '
  
  End Sub
  </code></pre>