C# Excel Automation: Retrieving rows after AutoFilter() with SpecialCells() does not seem to work properly

Question

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:

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

I successfully open the workbook and load the sheet from my C# application. After that I run the following code:

        Excel.Range range = xlSheet.UsedRange;
        range.AutoFilter(1, "AAA", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
        Excel.Range filteredRange = range.SpecialCells(Excel.XlCellType.xlCellTypeVisible);

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

        range.AutoFilter(1, "BBB", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
        Excel.Range filteredRange = range.SpecialCells(Excel.XlCellType.xlCellTypeVisible);

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?

Thanks in advance for any input you might have!

Answer 1

OK, I solved my own problem, but I would like to share the solution because some other poor soul might struggle with the same problem someday. Basically, my first idea that the problem might be with the rows not being consecutive led me to the right answer. As long as all the filtered values are right under the first row in the table, the SpecialCells() method returns one single area, and so the filteredRange shows in its Value2 member all of the desired values (in the test case from above, all the "AAA") rows. If, however, the filtered rows are further down in the table, as is the case with "BBB", the SpecialCells() method returns multiple areas, in this case two - first area containing only the column names line, and the second area containing the three "BBB" rows. The solution is to iterate over all the areas in filteredRange and extract/manipulate the values from there:

        for (int areaId = 1; areaId <= filteredRange.Areas.Count; areaId++)
        {
            Excel.Range areaRange = filteredRange.Areas.get_Item(areaId);
            object[,] areaValues = areaRange.Value2;
            // Do something with the values here...

So that's it. I hope that this helps someone else someday...

Answer 2

Try the following:

var rowcount = filteredRange.Count / filteredRange.Columns.Count;