After applying filter to excel range, rows not satisfying the filter condition get hidden and only rows satisfying the filter condition are visible. Refer our previous post for detailed Excel AutoFilter by C# information. Below is the sample snap of filtered data range of excel.
Here in this example, data is in A1:E11 range. Below C# code will help you to copy visible cells only in this filtered range.
excelSheet.Range["A1:E11"].SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeVisible).Copy();If you do not know the last used row in the data range, use below given C# code to copy only visible cells.
excelSheet.Range["A1:E" + excelSheet.Range["A1"].get_End(Microsoft.Office.Interop.Excel.XlDirection.xlDown).Row].SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeVisible).Copy();After that you can paste the copied range to destination sheet range by the help of below C# code.
excelSheetDest.Range["A1"].PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);