title: Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
body: <p>I have the following (on the surface of it, simple) task:</p>
  
  <p>Copy the values from a number of columns on a spreadsheet into a 2D array using VBA.</p>
  
  <p>To make life more interesting, the columns are not adjacent, but they are all of the same length.  Obviously one could do this by looping over every element in turn, but that seems very inelegant. I am hoping there is a more compact solution - but I struggle to find it.</p>
  
  <p>Here are some attempts of what I would consider "a simple approach" - for simplicity, I am putting the range as <code>A1:A5, D1:D5</code> - a total of 10 cells in two ranges.</p>
  
  <pre><code>Private Sub testIt()
    Dim r1, r2, ra, rd, rad
    Dim valString, valUnion, valBlock
    Set r1 = Range("A1:A5")
    Set r2 = Range("D1:D5")
    valString = Range("A1:A5,D1:D5").Value
    valUnion = Union(r1, r2).Value
    valBlock = Range("A1:D5").Value
  End Sub
  </code></pre>
  
  <p>When I look at each of these variables, the first two have dimension <code>(1 To 5, 1 To 1)</code> while the last one has <code>(1 To 5, 1 To 4)</code>. I was expecting to get <code>(1 To 5, 1 To 2)</code> for the first two, but that was not the case.</p>
  
  <p>I would be happy if I could loop over the data one column at the time, and assign all the values in one column to one column in the array - but I could not figure out how to do that either. Something like</p>
  
  <pre><code>cNames = Array("A", "D")
  ci = 1
  For Each c in columnNames
    vals( , ci) = Range(c &amp; "1:" &amp; c &amp; "5").Value
    ci = ci + 1
  Next c  
  </code></pre>
  
  <p>But that's not the right syntax.  The result I want to get would be achieved with</p>
  
  <pre><code>cNames = Array("A", "D")
  ci = 1
  For Each c in columnNames
    For ri = 1 To 5
      vals(ri , ci) = Range(c &amp; "1").offset(ri-1,0).Value
    Next ri
    ci = ci + 1
  Next c  
  </code></pre>
  
  <p>But that's pretty ugly. So here is my question:</p>
  
  <p>Is it possible to get the values of a "composite range" (multiple non-contiguous blocks) into an array - either all at once, or a columns at a time? If so, how do I do it?</p>
  
  <p>For extra bonus points - can anyone explain why the arrays returned in <code>testIt()</code> are dimensioned <code>Base 1</code>, whereas my VBA is set to <code>Option Base 0</code>? In other words - why are they not <code>(0 To 4, 0 To 0)</code>? Is this just one more inconsistency on the part of Microsoft?</p>