title: ReDim Preserve with multidimensional array in Excel VBA
body: <p>I can get this to work but am not sure if this is the correct or the most efficient way of doing this.</p>
  
  <p>Details: Looping through 151 rows then assigning column <code>A</code> and <code>B</code> only of those rows to a two dimensional array based on criteria in column <code>C</code>. With the criteria only 114 of the 151 rows are needed in the array.</p>
  
  <p>I know that with ReDim Preserve you can only resize the last array dimension and you can't change the number of dimensions at all. So I have sized the rows in the array to be the total 151 rows using the <code>LRow</code> variable but the actual rows I only need in the array is in variable <code>ValidRow</code> so it seems that (151-114) = 37 superfluous rows are in the array as a result of the ReDim Preserve line. I would like to make the array only as big as it needs to be which is 114 rows not 151 but not sure if this is possible see code below and any help much appreciated as I am new to arrays and have spent the best part of two days looking at this. Note: Columns are a constant no issue with them but rows vary.</p>
  
  <pre><code>Sub FillArray2()
  
  Dim Data() As Variant
  Dim ValidRow, r, LRow As Integer
  
  Sheets("Contract_BR_CONMaster").Select
  LRow = Range("A1").End(xlDown).Row '151 total rows
  
  Erase Data()
  
  For r = 2 To LRow
   If Cells(r, 3).Value &lt;&gt; "Bridge From" And Cells(r, 3).Value &lt;&gt; "Bridge To" Then
    ValidRow = ValidRow + 1
    ReDim Preserve Data(1 To LRow, 1 To 2)
    Data(ValidRow, 1) = Range("A" &amp; r).Value 'fills the array with col A
    Data(ValidRow, 2) = Range("B" &amp; r).Value 'fills the array with col B
   End If
  
  Next r
  
  ActiveWorkbook.Worksheets("Test").Range("A2:B" &amp; ValidRow + 1) = Data() 'assign after     loop has run through all data and assessed it
  
  End Sub
  </code></pre>