C# Office.Interop.Excel How do I loop through the AutoFilter'd returned rows?

Question

I have created the following AutoFilter:

excelRange = excelWorksheet.get_Range("G1");
excelRange.AutoFilter(Field: 7, Criteria1: "Part Number");
excelRange.AutoFilter(Field: 7, Criteria1: ">A*", Operator: Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd);
excelRange.AutoFilter(Field: 4, Criteria1: "<2023-01-01", Operator: Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd);

This reduced the rows down to a number let's say 935. How do I loop through the 935 rows produced by the AutoFilter?

Answer 1

@Alvord, Timothy, Welcome to Microsoft Q&A, you could try to use Range.SpecialCells(XlCellType, Object) Method to get filtered rows.

Here is a code example you could refer to.

Excel.Application app=new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open("test.xlsx");
Excel.Worksheet worksheet = workbook.Worksheets[1];
Excel.Range visibleCells = worksheet.UsedRange.SpecialCells(
                               Excel.XlCellType.xlCellTypeVisible,
                               Type.Missing);                                                                       
 Excel.Range visibleCells = worksheet.UsedRange.SpecialCells(
                               Excel.XlCellType.xlCellTypeVisible,
                               Type.Missing);
            foreach (Excel.Range area in visibleCells.Areas)
            {
                foreach (Excel.Range row in area.Rows)
                {
                    if (row.Row!=935)
                    {
                        Console.WriteLine(row.Row);
                    }
                    else
                    {
                        break;
                    }
                    
                }
            }
            workbook.Close();

Based on my test. it could produce the correct row's count.

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.