How can I get the Range of filtered rows using Excel Interop?

Accepted answer +8 upvotes

Once you filtered the range, you can access the cells that pass the filter criteria by making use of the Range.SpecialCells method, passing in a valued of ‘Excel.XlCellType.xlCellTypeVisible’ in order to get the visible cells.

Based on your example code, above, accessing the visible cells should look something like this:

Excel.Range visibleCells = sheet.UsedRange.SpecialCells(
                               Excel.XlCellType.xlCellTypeVisible, 
                               Type.Missing)

From there you can either access each cell in the visible range, via the ‘Range.Cells’ collection, or access each row, by first accessing the areas via the ‘Range.Areas’ collection and then iterating each row within the ‘Rows’ collection for each area. For example:

foreach (Excel.Range area in visibleCells.Areas)
{
    foreach (Excel.Range row in area.Rows)
    {
        // Process each un-filtered, visible row here.
    }
}

Hope this helps!

Mike