title: Excel VBA running very slow looping
body: <p>I have a small supplier price list which is effective data from x to y dates (in rows) with number of same products (in columns - quite a few). I am trying to copy the rows into another sheet but this time at a date level instead of range x/y that I need to export into a csv. Only limitation I have that I cannot change the format of price list.</p>
  
  <p>The vba code is working but its very slow although only I have a price list of 150 rows (sheet 1) that will translate into 6000 rows (in test), its taking hours to run the code. Could you advise how I could improve the performance? My vba skills are very basic and I have cobbled this together from other people code. </p>
  
  <pre><code>Sub ExpandData()
  
  Dim SourceRow, TargetRow As Long
  Dim LastDate, NextDate As Date
  Dim DateDiff, FillDate As Integer
  SourceRow = 4
  TargetRow = 4
  
  'Loop through source rows
  Do While Worksheets("Sheet1").Range("C" &amp; CStr(SourceRow)).Value &lt;&gt; ""
      LastDate = Worksheets("Sheet1").Range("F" &amp; CStr(SourceRow)).Value
      ' Check for the last row of data and use todays date if last row
      If Worksheets("Sheet1").Range("F" &amp; CStr(SourceRow + 1)).Value &lt;&gt; "" Then
          NextDate = Worksheets("Sheet1").Range("F" &amp; CStr(SourceRow + 1)).Value
      Else
          NextDate = Date
      End If
      DateDiff = NextDate - LastDate
      ' create a row in the target sheet for each date in between those in the source sheet
      For FillDate = 0 To DateDiff - 1
          Worksheets("test").Range("A" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("A" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("B" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("B" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("C" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("C" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("D" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("D" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("E" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("E" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("F" &amp; CStr(TargetRow)).Value = LastDate + FillDate
          Worksheets("test").Range("G" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("G" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("H" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("H" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("I" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("I" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("J" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("J" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("K" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("K" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("L" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("L" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("M" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("M" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("N" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("N" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("O" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("O" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("P" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("P" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("Q" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("Q" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("R" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("R" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("S" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("S" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("T" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("T" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("U" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("U" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("V" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("V" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("W" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("W" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("X" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("X" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("Y" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("Y" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("Z" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("Z" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("AA" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("AA" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("AB" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("AB" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("AC" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("AC" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("AD" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("AD" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("AE" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("AE" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("AF" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("AF" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("AG" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("AG" &amp; CStr(SourceRow)).Value
          Worksheets("test").Range("AH" &amp; CStr(TargetRow)).Value = Worksheets("Sheet1").Range("AH" &amp; CStr(SourceRow)).Value
        TargetRow = TargetRow + 1
      Next FillDate
  
      SourceRow = SourceRow + 1
  Loop
  
  End Sub
  </code></pre>