title: Excel convert 2d range to 1d range for use in functions
body: <p><strong>Surprisingly, searching Google for answers</strong>, as well as this site, has led to no obvious answer to this problem, so I thought I'd post it.</p>
  
  <p><em>I'm trying to create a macro (probably a UDF)</em> which converts a 2 dimensional cell range (like A1:C3) into a 1 dimensional range (a column or a row like A1:A10). This would be for use in functions such as <code>MATCH()</code> when a 2d array is needed e.g. to lookup a specific value in A1:C3. But I would like the function to be versatile so that I can slap it round any 2D array to make it 1D. Like <code>=INDEX(ARRAYDIMENSION(A1:C1),4)</code> or <code>=MATCH("hello",ARRAYDIMENSION(A1:C1),0)</code></p>
  
  <p><strong>I've already come up with a nasty approach</strong>; I slap each column of my array (for <code>A1:C3</code> that's <code>A1:A3</code>, <code>B1:B3</code>, <code>C1:C3</code>) underneath eachother in a new sheet (so A2 is copied to A2 of the new sheet, C3 is copied to A9, B1 to A4 etc.)</p>
  
  <p>I then return the reference of my new array (<code>Sheet2!A1:A9</code>) so <code>MATCH()</code> or <code>INDEX()</code> or whatever now looks there instead.</p>
  
  <p>It works but has some obvious flaws; adding a new sheet is time consuming for the macro and ugly, piling the individual columns on top of one another can't be the most efficient method. But in particular, the macro edits the sheet so can't be a UDF (I don't think) meaning I have to employ it as a Worksheet_Calculate Sub which is less self contained than just putting it in whatever formula needs it.</p>
  
  <hr>
  
  <p>I think that's relatively clear, please help/ advise on the best approach, I have only been VBAing for a few weeks so I'm not super slick yet.</p>