title: Out of Memory error after VBA Macro has run successfully 3 times
body: <p>I have a macro which is working as it's supposed to; reads in large amount of data from .txt files (upwards of 2mil data points), does some filtering and calculations and then outputs to a spreadsheet.
  However after running the macro 3 times VBA produces a Run-time error '7': Out of Memory.</p>
  
  <p>My temporary solution has been to save, close and reopen excel (2010) which has allowed me to run the program a few more times - but this isn't a viable option when handing the program to the client.</p>
  
  <p>This is the code, with the filtering taken out the middle. cellsArray is a variant array; strings and integers are input to it.</p>
  
  <pre><code>    For w = 0 To Listbox2count - 1
  
      Worksheets.Add(, ActiveSheet).Name = bookname
  
      ' open input text file
      myFile = ActiveWorkbook.path &amp; "\" &amp; SelectedTxtFileNames(w)
      Open myFile For Input As #1
  
      ReDim cellsArray(1048570, 12)
  
      Do While Not EOF(1)
  
          For i = 0 To 11
  
              ' some calculations here
  
              a = (i * ints) + counter + AMPMset * (ints * 11)
              cellsArray(a, 0) = bookname
              cellsArray(a, 1) = road
              cellsArray(a, 2) = approach
              cellsArray(a, 3) = detectorString
              cellsArray(a, 4) = dateString(0)
              cellsArray(a, 5) = IDate
              cellsArray(a, 8) = hours(i + 1)
              cellsArray(a, 9) = cars(0)
              If cellsArray(a, 9) = ":60" Then
                  cellsArray(a, 9) = ":00"
                  cellsArray(a, 8) = hours(i + 1) + 1
                  If cellsArray(a, 8) = 24 Then
                      cellsArray(a, 8) = 0
                  End If
              End If
              cellsArray(a, 10) = cars(i + 1)
              cellsArray(a, 11) = projectCode
          Next i
  
      Loop
  
      Range("A5:L" &amp; a + 5) = cellsArray
  
      Close #1
      ReDim cellsArray(0)
  
  Next w
  </code></pre>
  
  <p>The error is generated at this line:</p>
  
  <pre><code>Range("A5:L" &amp; a + 5) = cellsArray
  </code></pre>
  
  <p>The dimensions of cellsArray have been decided by the max number of rows before the data is output to a new spreadsheet.</p>
  
  <p>I've been watching the computer's memory usage using Task Manager while the macro runs; the memory usage peaks and falls as arrays are filled and emptied. The point when the Run-Time error occurs is usually when there is still available memory, so I'm really at a loss as to why this error is happening.</p>
  
  <p>The Run-time error is still generated if I write over the same spreadsheet over and over again; so no more memory is being used by the spreadsheet itself.</p>
  
  <p>It seems that a new chunk of memory is being used with each run of the macro; which I've only been able to clear by closing and reopening excel. Can anyone offer some solutions or advice?  </p>