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

Question

I'm using Excel Interop assemblies for my project, if I want to use auto filter with then thats possible using

sheet.UsedRange.AutoFilter(1,SheetNames[1],Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd,oMissing,false)

but how can I get the filtered rows ??

can anyone have idea??

Answer 1

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

Answer 2

I used as mentioned below, similar to what Mike told,

foreach (Excel.Range area in visibleCells.Areas)
{
 foreach(Excel.Range row in area.Rows)
 {
 int index = row.Row; // now index is the present Row index within the range
 string test = Mysheet.Cells[index,4].Values //// Mysheet is my present working sheet. After this test will contain the values pointing to the values.cells[index,4]
 }
}