I have a classic simple spreadsheet with several columns and several rows.
The first row of data contains field names – Labels. The remaining rows are filled with data. I manually select the labels in the cells in the first row of data, then use the Calc menu to create an AutoFilter by clicking
AutoFilter. This creates the simple filter with pull-down controls in each column as expected. The range for the filter created in this way implicitly includes only the rows which contain data – rows 7 to 11. And it works exactly as expected.
Sometimes, after manipulating the filter selections for the various columns, I end up with a somewhat complex filter comprising different settings for more than a few columns. I’d like a macro, configured to the ‘execute action’ of the ‘Clear Filter’ Button, to simply clear all the filter settings and revert to an unfiltered display.
Note, to the extent that it is important, that the first several rows of the sheet DO NOT include ‘data’ of interest to the filter. Note also that I have NOT defined a Calc Database using
Define Range. I simply selected the Labels on row 6 and created the AutoFilter.
I know that I can create a
Database Range and use a
FilterFields to do all sorts of things, including clearing the filter settings. But is a
Database Range absolutely required? I dislike creating names and data that are not absolutely required because they increase the complexity of a workbook and end up making maintenance more difficult. Is it possible to manipulate these AutoFilter settings WITHOUT CREATING A DATABASE RANGE?
This recent post by @newbie-02 asks essentially the same question. The one suggested answer, posted by @erAck, indicates that some sort of a hidden Database Range is created by Calc. He then refers to an apparently unpublished
XUnnamedDatabaseRange interface. Though I dislike the idea of using unpublished APIs, this answer is intriguing enough to pursue, if only to give me a better understanding of how to manipulate Calc’s filters. Sadly, the answer gets into a level of abstraction that is beyond my abilities. Can anyone offer a code example that would instantiate the
XunnamedDatabaseRange interface and allow the use of its