C# Excel - How to copy the rows filtered by the AutoFilter() method

Question

Hi

I've been using the AutoFilter() method with no problem so far. but i wasnt able to mark or get the range of the filtered rows, to count/copy/delete them.

I've seen many posts on this issue with VBA, but non for C#.

After filtering, i tried to get the range by any of those lines

range = ws.UsedRange.CurrentRegion.SpecialCells(Excel.XlCellType.xlCellTypeVisible, missing);

range = ws.AutoFilter.Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible, missing);

range = ws.Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible, missing);

and i have even tried other ways that i dont even remember. after marking the range i tried to count the rows by doing:

range.Rows.Count

and in each case, i got the total number of lines of the sheet, sometimes i got 65536 (office 2003) and sometimes just 1 but surly not the number of rows that i filtered.

Any clue?

I must use it with office 2003 with Object Library 11.

Thanks!

Answer 1

Ok. after a long sleep. i got some power to try more directions, this time without getting directions from other forums and google.

Try this:

• Apply the autoFilter on the used range of the whole workSheet

Excel.Range range = workSheet.UsedRange;

• Apply the autoFilter on the range

 range.AutoFilter(1, criteriaString ,

Excel.XlAutoFilterOperator.xlAnd, missing, true);

(this will filter the rows according to column 1 with criteriaString as the criteria)

• Now you want to get the filtered range:

Excel.Range filteredRange = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeVisible,

missing);

• Now, you can access filteredRange.Rows.Count integer to count the rows, and do fun stuff.

THATS IT! it had to be simple then i ever thought it would.

Thanks