title: C# Excel Automation: Retrieving rows after AutoFilter() with SpecialCells() does not seem to work properly
body: <p>First time poster here :). I'm having the following trouble automating Excel 2010 from a WinForms C# application. My test table looks the following way:</p>
  
  <pre><code>Index    Value1  Value2
  AAA        2       3   
  AAA        3       4
  AAA        3       8
  BBB        2       2
  BBB        5       6
  BBB        3       5
  CCC        1       2
  CCC        2       2
  DDD        2       5
  </code></pre>
  
  <p>I successfully open the workbook and load the sheet from my C# application. After that I run the following code:</p>
  
  <pre><code>        Excel.Range range = xlSheet.UsedRange;
          range.AutoFilter(1, "AAA", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
          Excel.Range filteredRange = range.SpecialCells(Excel.XlCellType.xlCellTypeVisible);
  </code></pre>
  
  <p>This works as expected, and filterRange now contains the first four rows of my test table (the column names and all the "AAA" rows). If, however, I try to use AutoFilter to get all the "BBB" rows, like this</p>
  
  <pre><code>        range.AutoFilter(1, "BBB", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
          Excel.Range filteredRange = range.SpecialCells(Excel.XlCellType.xlCellTypeVisible);
  </code></pre>
  
  <p>I end up with only the firs row of the table (column names) in the filteredRange Range. If I actually open the table in Excel I can see that it is correctly filtered (the "BBB" rows) are filtered, but somehow the Range.SpecialCells() method does not behave as expected and only returns the first row. 
  I've tried everything I can think of, but since I'm new to Excel automation, maybe I'm missing something, so I thought you guys might be able to help. The only thing that comes to my mind is that in the first case ("AAA"), all visible rows are consecutive - the column names are on row 1 and the "AAA" rows are 2, 3, and 4 respectively. In the second case, the names are on row 1 but the "BBB" rows are with index 5, 6 and 7, i.e. there is an "hole" in the table. Could this perhaps affect the SpecialCells() method?</p>
  
  <p>Thanks in advance for any input you might have!</p>