title: Autofilter: Range class failure on "<>" within the code's generated arrays for the criteria
body: <p>My code reads the values of cells within 3 column until it meets an empty cell and fills 3 arrays with the values. I use <code>&lt;&gt;</code> when I don't want to filter through a column or <code>&lt;&gt;item</code> if I want all but the one.</p>
  
  <p>The problem is that if an array contains the negation symbol, it says the Range class has failed. However, if I straight up write <code>&lt;&gt;</code> / <code>&lt;&gt;Item</code> into criteria it goes through. Same if I use <code>Criteria1:=array(filter1, filter2,...)</code> and either of the "filter" values has one or both of the two options. Just when I do <code>Criteria1:=arrayName</code> it fails. Importantly, it does not fail when it simply just contains "Item". It also does not fail when I write it in as <code>Criteria:=arrayName(1)</code>, and the index one (first) contains the negation.
  I'm failing to get my head around what is going on. Sorry if the answer is trivial.</p>
  
  <pre class="lang-vb prettyprint-override"><code>Dim sel As Double
  Dim i As Integer
  Dim distri() As String
  Dim purp() As String
  Dim prod() As String
  
  'user selects a value in the workbook, the value is found in another workbook
  'There are then criterias in 2nd, 3rd and 4th columns by which sheet "data" is filtered by
  sel = Selection.Value
  Windows("different_workbook.xlsm").Activate
  Sheets("values").Select
  
  Cells.Find(What:=sel, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
      xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
      , SearchFormat:=False).Activate
  Let selCol = Selection.Column
  Let selRow = Selection.Row
  Sheets("calculations").Select
  Cells(selRow, selCol).Select
  Let end = False
  i = 0
  While end = False 'going through the criteria top &gt; down until there is a space between the criterias, some values need multiple filtered items
      If IsEmpty(Cells(selRow + i, 2).Value) Or IsEmpty(Cells(selRow + i, 3).Value) Or IsEmpty(Cells(selRow + i, 4).Value) Then
          end = True
      Else
          i = i + 1
      End If
  Wend 'now the code knows how many criterias to expect
  ReDim Preserve distri(1 To i)
  ReDim Preserve purp(1 To i)
  ReDim Preserve prod(1 To i)
  For j = 1 To i
      distri(j) = Cells(selRow + j - 1, 2).Value
      purp(j) = Cells(selRow + j - 1, 3).Value
      prod(j) = Cells(selRow + j - 1, 4).Value
  Next
  Sheets("data").Select 'the three commands where the error occurs
  ActiveSheet.Rows(3).AutoFilter Field:=4, _
  Criteria1:=distri, _
  Operator:=xlFilterValues
  ActiveSheet.Rows(3).AutoFilter Field:=5, _
  Criteria1:=purp, _
  Operator:=xlFilterValues
  ActiveSheet.Rows(3).AutoFilter Field:=8, _
  Criteria1:=prod, _
  Operator:=xlFilterValues
  </code></pre>
  
  <p>I'm expecting it to use <code>&lt;&gt;</code> as "passes" for filtration. It did work for me in my older code, where I hard coded which line has how many rows of criteria, each of the criteria I saved into a variable and then made the <code>:=array(filter1,..)</code> example. Now I decided to make it a more flexible macro and easier to read and shorter code etc.</p>