title: Filtering 2D Arrays in Excel VBA
body: <p>Using Excel and VBA, I wanted some advice on how to best filter data in an array (in the same sort of way one might use a pivot table) strictly using VBA.  I am creating a UserForm that is going to make some data decisions based on currently existing data. I can visualize how to do it well enough but am not that versed in VBA programming.  </p>
  
  <p>Here is an example</p>
  
  <pre><code>A       B       C
  bob     12      Small
  sam     16      Large
  sally   1346    Large
  sam     13      Small
  sally   65      Medium
  bob     1       Medium
  </code></pre>
  
  <p>To grab the data in an Array, I could use</p>
  
  <pre><code>Dim my_array As Variant
  
  my_array = Range("A1").CurrentRegion
  </code></pre>
  
  <p>Now, I am familiar with looping through 2D arrays, but I wondered: <strong>what the most effective way to filter 2D array data</strong> (without looping through the array time and again)?  </p>
  
  <p>For example, how do I get would be to say get this kind of data:</p>
  
  <pre><code>data_for_sally As Variant 'rows with sally as name in ColA
  data_for_sally_less_than_ten As Variant ' all rows with sally's name in ColA and colB &lt; 10
  data_for_all_mediums as Variant ' all rows where ColC is Medium
  </code></pre>
  
  <p>Suggestions?  I could work this out with a bunch of custom functions and loops but I thought there must be a better way.  Thanks.</p>