I would like to know how to determine the original criteria used when someone filters with a 'Top10' autofilter.
The set of filters called 'Top10' actually includes four different filter types Top10Items, Bottom10Items, Top10Percent, and Bottom10Percent. Note that the names for these filters is confusing since you can use any number as the criteria not just 10. So more appropriate names would be something like TopNItems. The criteria is applied to the full data set and is not dependent on other filters.
Normally when you have a filter, you can see what the original criteria is by checking the filter object using some code like ActiveSheet.AutoFilter.Filters(1).Criteria1
the problem I am having is that when using a 'Top10' filter, the Criteria1 property actually gets changed to a <= or >= condition based on the numbers in your column and it doesn't seem possible to determine what the original Criteria used. So for example, if you have the numbers 1 to 100 in a column and set a Top10Items filter with Criteria1=5, then when you check the filter object after creation, it will have the Criteria1 as >=96.
This information is definitely not in the Filter object, and as far as I can tell it is also not in the Range object for the header cell. Note that when you hover over the filter, you see a screen tip (that looks similar to a comment) that shows the original filter criteria, but I can't find where the text for that screen tip is stored in the Range or AutoFilter objects. Note that counting the number of filtered items will not work either since duplicates are treated in an odd manner and the same number of items can be shown for different Top10Percent filters based on the rounding.
Does anyone know how to retrieve the original criteria for such a filter? I need to know it so that I can apply it programatically to a table on another worksheet. The problem is described in more detail here in the section entitled 'Discovery #4: Top 10 Settings Are Weird': http://yoursumbuddy.com/autofilter-vba-operator-parameters/
The set of filters called 'Top10' actually includes four different filter types Top10Items, Bottom10Items, Top10Percent, and Bottom10Percent. Note that the names for these filters is confusing since you can use any number as the criteria not just 10. So more appropriate names would be something like TopNItems. The criteria is applied to the full data set and is not dependent on other filters.
Normally when you have a filter, you can see what the original criteria is by checking the filter object using some code like ActiveSheet.AutoFilter.Filters(1).Criteria1
the problem I am having is that when using a 'Top10' filter, the Criteria1 property actually gets changed to a <= or >= condition based on the numbers in your column and it doesn't seem possible to determine what the original Criteria used. So for example, if you have the numbers 1 to 100 in a column and set a Top10Items filter with Criteria1=5, then when you check the filter object after creation, it will have the Criteria1 as >=96.
This information is definitely not in the Filter object, and as far as I can tell it is also not in the Range object for the header cell. Note that when you hover over the filter, you see a screen tip (that looks similar to a comment) that shows the original filter criteria, but I can't find where the text for that screen tip is stored in the Range or AutoFilter objects. Note that counting the number of filtered items will not work either since duplicates are treated in an odd manner and the same number of items can be shown for different Top10Percent filters based on the rounding.
Does anyone know how to retrieve the original criteria for such a filter? I need to know it so that I can apply it programatically to a table on another worksheet. The problem is described in more detail here in the section entitled 'Discovery #4: Top 10 Settings Are Weird': http://yoursumbuddy.com/autofilter-vba-operator-parameters/
