How to copy the rows filtered by the AutoFilter() method

I've been using the AutoFilter() method with no problem so far. but I wasn't 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 none for C#.

After filtering, I tried to get the range by any of these lines:

range = ws.UsedRange.CurrentRegion.SpecialCells(Excel.XlCellType.xlCellTypeVisible, missing);
range = ws.AutoFilter.Range.SpecialCells(Excel.XlCellT开发者_如何学编程ype.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 surely not the number of rows that I filtered.

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

---

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

---

For deleting the rows , it is just range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);

---

after autofilter, i would like to name a term "COGI" only in range "N" in the filtered rows.how to write to just pick up that range "N" and insert a term "COGI"