title: macro extract data block
body: <p>I have worked on this problem for my entire day and can't solve it. </p>
  
  <p>The input data consists of several data blocks with the same number of rows and columns. Each data block has its name in the first line within the block. Besides, they are further separated by a blank row.   </p>
  
  <pre><code>block1
  name score value
   a     2     3
   b     3     5
   c     1     6
  
  block2
  name score value
   a     4     6
   b     7     8
   c     2     6
  
  block3
  name score value
   a     5     4
   b     7     8
   c     2     9
  </code></pre>
  
  <p>The desired output is to extract the name and value column of each block, and then parallel them in columns. Like this:</p>
  
  <pre><code>value  block1  block2 block3
   a       3     6      4
   b       5     8      8
   c       6     6      9
  </code></pre>
  
  <p>Thanks for your help!</p>
  
  <p><strong>UPDATE</strong>
  Thanks for your answer, Tony, and others!
  I just have another requirement. It is possible that some row in some tables are missing. In other words, as you mentioned previously, the row number may vary. Is it possible to fill in the corresponding cell in these tables with NA? i.e. the new input is like:</p>
  
  <pre><code>block1
  name score value
  a     2     3
  c     1     6
  
  block2
  name score value
  a     4     6
  b     7     8
  c     2     6
  
  block3
  name score value
  a     5     4
  b     7     8
  </code></pre>
  
  <p>The desired output now is like this:</p>
  
  <pre><code>value  block1  block2 block3
  a       3       6      4
  b       NA      8      8
  c       6       6      NA
  </code></pre>
  
  <p><strong>UPDATE on Jul.3 (If it's inappropriate to make the question too long, I will move this part and make it a new question)</strong></p>
  
  <pre><code> block1
  name score value
   a     2     3
   b     3     5
   c     1     6
  
  block2
  name score value
   a     4     6
   b     7     8
   c     2     6
  
  block3
  name score value
   a     5     4
   b     7     8
   c     2     9
  </code></pre>
  
  <p>How can I pull both the value and its corresponding score and put them into one cell? Like this: The code indicates that the value is put into an dynamic array. Then the .range is assigned to this array. My first thought is to construct another array to store the value of the "score" column. Then loop through each element in both array, and concatenate them together. However, it seems that VBA does allow me to loop through the array, since its dimension is not defined. I tried REDIM, but it did not work. </p>
  
  <pre><code>value  block1   block2    block3
   a       3(2)     6(4)      4(5)
   b       5(3)     8(7)      8(7)
   c       6(1)     6(2)      9(2)
  </code></pre>