title: VBA on Excel "Out of Memory" error
body: <p>I'm on Excel 2010, on an admittedly very large sheet (400k rows X 20 columns).</p>
  
  <p>My code aims to:</p>
  
  <ul>
  <li>load the entire sheet into an array</li>
  <li>examine every row for a certain criteria</li>
  <li>rows which qualify are copied to another array</li>
  <li>finally return the second array back to another sheet</li>
  <li>the second array will end up being roughly 90% of the original</li>
  </ul>
  
  <p>I wrote a definition of two variable arrays as variants
  And tried to initialize them by copying the sheet's content twice.</p>
  
  <p>first copy works, but by the second one I hit an error of "Out of memory".</p>
  
  <p>Any ideas if there's a workaround? or is this just a limitation of VBA/ Excel.</p>
  
  <p>Is there a way to not pre-define / initialize the destination array, and instead, let it "grow" with every successful qualification of the criteria? (On a scale of this magnitude).</p>
  
  <pre><code>Sub CopyPending()
  Dim LastRow As Long
  Dim LastCol As Integer
  Dim AllRange() As Variant
  Dim CopyRange() As Variant
  Dim i As Long
  Dim x As Long
  Dim z As Long
  
  LastCol = 21
  LastRow = ActiveSheet.UsedRange.Rows.Count
  
  AllRange = Range(Cells(2, 1), Cells(LastRow, LastCol)).Value
  CopyRange = Range(Cells(2, 1), Cells(LastRow, LastCol)).Value ''' ERROR TRIGGER
  
  i = 1
  x = 1
  z = 1
  
  For i = LBound(AllRange) To UBound(AllRange) - 1
    If AllRange(i, 7) = "TestCriteria" Then
      For z = 1 To LastCol
        CopyRange(x, z) = AllRange(i, z)
      Next z
      x = x + 1
    End If
  Next i
  
  With Sheets(2)
    .Range(.Cells(2, 1), .Cells(x, LastCol)).Value = CopyRange
  End With
  
  End Sub
  </code></pre>