Macro to manipulate an AutoFilter without a Database Range

[wrighch]

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 Data > 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 Data > 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 FilterDescriptor and 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 hasByTable and getByTable methods?

[mauricio]

Using the data ranges is simple and is the best way to use the filters. Just out of curiosity, why would you want to use the filters without a database range?

[wrighch]

Good question @mauricio. But I don’t want my macro to do anything more than clear the filters. Setting the AutoFilter is a simple matter of selecting the Labels and then Menu > Data > AutoFilter. But clearing ALL filters requires me to sequentially select each active pull-down individually and clear it. That becomes tedious when 3 or 4 columns have separate filters applied. Of course, I could clear them all at once by removing and replacing the AutoFilter. But that’s almost as tedious – especially when the Labels are not in Row 1 as mine rarely are. My goal is a 1-click button that will clear all of the filters. Calc will create the AutoFilter without my having to define the Database Range. So I’d rather not have to define the name just to clear the filter. It’s just one more name to maintain. As I noted in my post, I’d prefer not to have to define a Database Range if all I’m trying to do is clear the filter. And the the suggestion below by @schiavinatto is pretty slick.

[schiavinatto]

@wrighch, follows an example using the macro below.

Sub ClearAutoFilter
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint" : args1(0).Value = "$A$6" '<= First cell in the table
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoToCell", "", 0, args1())
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:DataFilterAutoFilter", "", 0, Array())
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:DataFilterAutoFilter", "", 0, Array())
End Sub

Test file|attachment

In reality the macro removes the autofilter and replaces it.

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.

[wrighch]

Very nice @schiavinatto. Your example is elegant in its simplicity. It’s wonderful when someone helps me move outside of the mental box I had put myself in. I’ll probably end up using your concept. But I’d still like someone to show me how to gain access to the ‘hidden’ Database Range names. I knew how to do that in Excel VBA but can’t seem to figure it out in LibreOffice.

[schiavinatto]

“But I’d still like someone to show me how to gain access to the ‘hidden’ Database Range names.”

I did not understand, explain better.

[wrighch]

The answer by @erAck in the link I provided in my OP (this link) indicates: “These anonymous ranges can not be enumerated or obtained by name, but there is the css::sheet::XUnnamedDatabaseRanges interface at which if hasByTable(nTab) returns true the getByTable(nTab) call returns the associated css::sheet::XDatabaseRange in its Any.”

I’d LOVE to see an example of code that uses the hasByTable and getByTable methods of the XUnnamedDatabaseRanges interface to obtain the so-called anonymous range. I don’t know how to create an instance of that interface.

[mauricio]

ok, try:

Sub main()
	sel = ThisComponent.CurrentController.Selection
	range = get_range(sel)
	fd = range.createFilterDescriptor(True)
	range.filter(fd)
End Sub


Function get_range(cell)
	cursor = cell.SpreadSheet.createCursorByRange(cell)
	cursor.collapseToCurrentRegion()
	get_range = cursor
End Function

[wrighch]

Very nice example @mauricio. But I decided to go with the code @schiavinatto proposed.

[newbie-02]

(small edit for readability …)

just one more … say ‘kiss’? … solution …

sub toggle_autofilter

dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:DataFilterAutoFilter", "", 0, Array())

end sub 'toggle_autofilter

assign to keyboard shortcut - i’d choose ‘alt-F1’ (Filter einschalten) - and your’e done and fast … it toggles, filter on will go off, and on re-on will be cleared,

(sorry for the unelegant code … just macro recorder …)

be extremely careful with filterfields and such stuff, the have a bug stepping in once your range has different offset from top and left border of the sheet and are unreliable across save - reload,

(sorry for the unelegant code … just macro recorder …)

P.S. ‘solved marks’ and ‘likes’ welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
‘answer’ only if you found a solution yourself …

[newbie-02]

pls. don’t laugh, i’m not! an expert, but from a quick look there interface to x_unnamed_database_ranges i’d suspect it’s just a ‘placeholder’ for future enhancements …