title: Excel VBA AutoFilter Array
body: <p>I'm working with AutoFilters in VBA with Microsoft Excel. I'm having an issue with how it handles arrays for filtering; I've watered down my original context to a simplified version (originally in hopes of understanding the issue):</p>
  
  <p>In a worksheet in Range A1:A5, let's say we have <code>Fruit</code>, <code>Apple</code>, <code>Banana</code>, <code>Orange</code>, and <code>Pear</code>, respectively. An AutoFilter has been applied such that <code>Fruit</code> is a column header.</p>
  
  <p>Running the below code returns the expected results (<code>Apple</code>, <code>Banana</code>, and <code>Orange</code> but not <code>Pear</code>):</p>
  
  <pre><code>Range("A1").Select
  ActiveSheet.Range("A1:A5").AutoFilter Field:=1, _
      Criteria1:=Array("=*an*", "=*app*"), Operator:=xlFilterValues
  </code></pre>
  
  <p>In the project I'm working on, the filter criteria are passed in as String variables, which works as the above. The issue is that not every criterion is applied every time, so some of them should have no effect.</p>
  
  <p>For example:</p>
  
  <pre><code>Dim A As String, B As String, C As String
  A = "=*an*"
  B = Empty
  C = "=*ap*"
  
  Range("A1").Select
  ActiveSheet.Range("A1:A5").AutoFilter Field:=1, _
      Criteria1:=Array(A, B, C), Operator:=xlFilterValues
  </code></pre>
  
  <p>With <code>B</code> thrown into the mix, filtering returns no records (whether it's left null, set to <code>Empty</code>, or uses wildcards like <code>=*</code>). Replacing <code>B</code> with <code>Empty</code> (hard-coded) in the actual criteria array returns the expected result, however.</p>
  
  <p>I've used similar code in the past (and had it work), albeit with an AutoFilter that was part of a ListObject. At this point, the only thing I can think of is concatenating the filters into a String with delimiters and splitting it into an Array variable (so that it's the precise size, since an un-set item in the collection messes that up the same way a standard variable does). But that seems unintuitive and cumbersome.</p>
  
  <p>Am I missing something obvious here?</p>