C# - Excel - how to get the criterias available for the autofilter() method

Question

Hi

I want to use the autoFilter() method to filter some rows.
I've managed to activate the autoFilter() method on a selected range (the whole sheet) with no problem.
The method i use on a selected range looks like this:

range.AutoFilter(5, myCriteria, Excel.XlAutoFilterOperator.xlAnd, missing, true);

where:

5 is the column index,
myCriteria is the object to filter with.

This works fine when I know what myCriteria is before I call the method.

But what if I don't know what criteria to use, and I want to get it from the values available in a particular column (column #5 for example)

Note that when you use auto filter in Excel manually, you get a drop-down menu which contains the available choices.
For example, if there are many rows, that column #5 may have values like 0, 5, 10, 15, 20, but I can't know this in advance.

The hard way will be to iterate through all the values in that column and build a list of unique values.

Is there a fast and easy way to do it?

(I have to use Excel 2003 with "Microsoft Excel 11.0 Object Library" )

Thanks

Answer 1

You can programatically use advanced filter with Unique Records Only ticked, but that will give you a list on a sheet.

However, if you think about it, does it make sense at all? Why would you ever want to filter a list using a random value from this very list?

If the user wants to make a selection, they will provide a value for you to filter with.
If you want to filter some records programmatically, then again, you already know what your filter condition is going to be. And if you don't, you don't have to filter the list.