How to get range of filtered rows using Excel Interop?

Question

I use Excel Interop assemblies for my project if I want to use an automatic filter with which I can use

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 an idea?

Answer 1

After filtering the range, you can access cells that pass filter criteria using the Range.SpecialCells method, passing the value "Excel.XlCellType.xlCellTypeVisible" to get the visible cells.

Based on your sample code above, access to 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, or through the Range.Cells collection, or access each row by first accessing the areas through 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