title: Copy Two dimensional array to another array based on criteria in VBA
body: <p>I would like to copy data from one sheet to another. </p>
  
  <p>I put the range that I want to copy into an array (LookupSource) because it's faster to work on arrays than looping through cells.</p>
  
  <p>After filling my two dimensional array (LookupSource), I would like to keep only some records based on critieria (Column A = 10000), so I am trying to copy from LookupSource, the rows that fetch this criteria to the two dimensional array (DataToCopy) which will be copied to the destination sheet.</p>
  
  <p>My problem is that I am not able to do that because as it seems I am not able to make a dynamic resize for the first dimension (rows) of the second array (DataToCopy).</p>
  
  <p>Any Idea how to fill DataToCopy from LookupSource based on my condition ?</p>
  
  <p>The error "index out of range" that I am getting is at the Line :     <code>ReDim Preserve DataToCopy(1 to j, 1 to 6)</code></p>
  
  <p>not at first time, but on second time that I enter the For loop after the Next I
  I suppose it's because the J is variable and I am not allowed to change the first dimension of the array.</p>
  
  <p>How to deal with that ?</p>
  
  <p>Any better Idea from what I am doing ?</p>
  
  <p>to give you an example here is a small part of the sheet that I want to copy (I took only 8 rows, but in real there thousands). I want to copy only the rows that have 10000 in column A.</p>
  
  <p><a href="https://i.stack.imgur.com/VtOvA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VtOvA.png" alt="enter image description here"></a></p>
  
  <p>Here is my code</p>
  
  <pre><code>Dim LookupSource as Variant      
  Dim DataToCopy() As Variant        
  Dim i As Long
  Dim j As Long
  
  
  With MySheet
  'MyRange is a defined name that reprensent column A, B, C, D, E, F
  LookupSource = .Range(.Range("MyRange")(1, 1), .Range("MyRange")(8, 6)).Value2
  
  j = 1
  
  For i = LBound(LookupSource) To UBound(LookupSource)
  
  If LookupSource(i, 1) = 10073 Then
  ReDim Preserve DataToCopy(1 to j, 1 to 6)
  DataToCopy(j, 1) = LookupSource(i, 1)
  DataToCopy(j, 2) = LookupSource(i, 2)
  DataToCopy(j, 3) = LookupSource(i, 3)
  DataToCopy(j, 4) = LookupSource(i, 4)
  DataToCopy(j, 5) = LookupSource(i, 5)
  DataToCopy(j, 6) = LookupSource(i, 6)
  j = j + 1
  End If
  
  Next i
  
  end with
  </code></pre>