CODEWITHSUNDEEP
c#excel-interopautofilter
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10297

How can I restrict an AutoFilter in Excel Interop to only act on the rows in one column?

I want to allow filtering on the values in one column (A, or 1), from a specific row (6). I can provide filters for all the columns on a row like so:

Range sixthRow = (Range)_xlSheet.Rows[6];
sixthRow.AutoFilter(1,
        Type.Missing,
        XlAutoFilterOperator.xlAnd,
        Type.Missing,
        true);

...or like so:

Range column1Row6 = _xlSheet.Range[_xlSheet.Cells[6, 1], _xlSheet.Cells[6, 1]];
column1Row6.AutoFilter(1,
        Type.Missing,
        XlAutoFilterOperator.xlAnd,
        Type.Missing,
        true);

...so that it looks like this:

enter image description here

...but I only want the filtering for the first column, from row 6 (the "from row 6" part is working fine), not columns 2, 3, 4, and 5 (and there could be more).

How can I have the filter only on the first column (A, or 1) not over the entire row?

Upvotes: 2

Views: 1771

Answers (1)

Hambone
Hambone

Reputation: 16397

If I understand your question correctly, there is a hack I use in normal Excel to accomplish filtering on a single column. I used to highlight the entire column and hit the filter button, which is a real pain when you have tens of thousands of rows of data.

As you probably know, when you have only one cell highlighted, it autofilters the entire row, as if to say any one cell means "nothing in particular" was highlighted.

Quite by accident, I discovered that if you highlight any range that extends beyond one cell, it tries to figure out what you meant. In this case, if you highlight the header row (A5) and one cell immediately below that and then hit filter, it will have the same effect as highlighting the whole column.

So, in C# interop terms, this would be the equivalent of that action:

_xlSheet.Range[_xlSheet.Cells[5, 1], _xlSheet.Cells[6, 1]].AutoFilter();

Upvotes: 2

Related Questions