Strange behavior of resetting filter with macro

English
#1

Hi!

I’ve added a macro to reset filter on a sheet to my existing calc document, but found it works strange. On some sheets it works properly, on other not: when run, all rows became visible, but filter is not reset.
I’m using 32-bit version 6.0.7.3 mostly (due to old laptop), but I have tested it with 7.3.7.2 64-bit, but with the same behavior.

Here is the macros (first row is specified because some sheets contain filter in the first row, some in second, but both work proper and improper for different sheets)

function getUsedArea(sheet,optional startRow) ' macro to get range of data
	cur = sheet.createCursor()
	cur.gotoStartOfUsedArea(False)
	cur.gotoEndOfUsedArea(True)
	adr = cur.getRangeAddress()
	if isMissing(startRow) then startRow = adr.StartRow
	getUsedArea = sheet.getCellRangeByPosition(adr.StartColumn,startRow,adr.EndColumn,adr.EndRow)
end function

sub clear_filter(sheet,startRow) ' macro to clear filter
	rng = getUsedArea(sheet,startRow)
	fd = rng.createFilterDescriptor(True)
	fd.ContainsHeader = True
	rng.filter(fd)
end sub

sub clear_filter_0 ' macro to clear filter if headers are in the 1st row
	clear_filter(ThisComponent.CurrentController.getActiveSheet,0)
end sub

sub clear_filter_1 ' macro to clear filter if headers are in the 2nd row
	clear_filter(ThisComponent.CurrentController.getActiveSheet,1)
end sub

I’ve cleared data and extra stuff from my document to make the example. How to reproduce: set filter on any sheet and press button “all” - see the result.

I would be thankful if somebody can explain why it works in this way and how it should be done properly.
filter_test.ods (34.5 KB)

#2

Not a solution, but an observation that may help you get closer to a solution: I copied the contents of your sheet “a” to a new workbook and copied your macros to a new module in that workbook. When I run your macros there, it clears the filter correctly (in addition to making the hidden rows visible again). Could it be that there is something about your workbook that is preventing the filter from being cleared?
filter_test_2.ods (18.6 KB)

#3

Here’s a version with a form control to run the macro. It works this way too.
filter_test_2_with_form_control.ods (29.5 KB)

#4

Thanks!
I’ll try your examples.
Generally the goal is to simply reset filter without it removing. And it appeared not simple to me :slight_smile: Also I found different behavior when filter is set via macro and when it is set manually by selecting conditions… Too complex object model for me yet :slight_smile:

#5

Try also this.

Option Explicit

' Clears or removes sheet filter.
Sub Sheet_ClearAutoFilter(ByVal oSheet As Object, Optional ByVal bRemove As Boolean) 
  Dim oDBRange As Object, oFilterDesc As Object
  If IsMissing(bRemove) Then bRemove=False
  oDBRange=GetSheetFilterDBRange(oSheet)
  If Not (oDBRange Is Nothing) Then
    If oDBRange.AutoFilter Then  
      oFilterDesc=oDBRange.ReferredCells.createFilterDescriptor(false)
      oFilterDesc.setFilterFields Array()
      oDBRange.ReferredCells.filter oFilterDesc
      If bRemove Then oDBRange.autofilter=False
    End If  
  End If
End Sub

Sub TestSheet_ClearAutoFilter()
  Sheet_ClearAutoFilter ThisComponent.CurrentController.ActiveSheet, False
End Sub

' Returns the DataBaseRange for sheet filter or Nothing.
Function GetSheetFilterDBRange(oSheet) as Object
  Dim i as Long, oDoc As Object
  GetSheetFilterDBRange=Nothing
  oDoc=oSheet.Drawpage.Forms.Parent
  i=oSheet.RangeAddress.Sheet
  With oDoc.getPropertyValue("UnnamedDatabaseRanges")
    If .hasByTable(i) Then GetSheetFilterDBRange=.getByTable(i)
  End With
End Function
2 Likes
#6

Thanks, I’ll try. I met a snippet with dbRange already, but it fails because there was no dbRange defined. Maybe your code with (oDBRange Is Nothing) checking will be better.
Does autofilter define dbRange automatically or I have to define it for every filtered sheet?

#7

(I’m not an expert and I use the AutoFilter tool very rarely.)
Out of abstract interest I did a bit of research (under V 24.2).
…
The API docu only mentions a propertty AutoFilter (Boolean, enabled or not) for named DataBaseRange objects…

The way to create/remove an AutoFilter via the UI (Ctrl+Shift+L by default) may be one of the tricks working with hidden objects.
Also the recorder only handles the creation/removal but no further operations with ranges filtered this way.

Thus: If you need to reset an AutoFilter by user code, you may need to define the respective range as a DB-range. Even then there will be complications and the filter settings will persist a disable/enable cycle. And I didn’t find a way to change that.
In short: AutoFilter isn’t made for tampering by user code!

#8

Maybe that’s correct…
I’ve been using 2 ways to filter records with macro (not using dbRange way), but there is a trouble to mix them with embedded autofilter. And that’s too bad…

My calc document is a year old, and went through all my study of macro basic from scratch, so, I even suppose some sheets may have some hidden residual properties which make them to behave differently. That’s why I make the example by clearing data, not creating the new clean document.

#9

To clear the filter on the current sheet, simply run the macro TestSheet_ClearAutoFilter.

#11

This means my code is not a user code. :slightly_smiling_face:

#12

:clap:
Seems it means I was on error. (First time in life, of course.)

1 Like
#13

How did you get to that goal?
That was convoluted beyond my abilities,

#14

The central place is the GetSheetFilterDBRange function. I learned about this algorithm from @erAck (to whom many thanks).
See also tdf#147257.

#10

Well, I made a separate very simple spreadsheet from scratch and seems as filter works as expected.

You may try: point row with vendor at sheet “v” and press the button, on the “d” sheet you may add conditions, and than clear the filter.
For example: select “Atmel” and then you may select only “x51” architecture from selected devices. Then you may simply reset the filter, etc.

Possibly the problem is only with certain sheets in my document, I’ll try to recreate them. As this issue is very mysterous and specific, let it be marked as “solved” :slight_smile:
play_with_filter.ods (17.8 KB)

Thanks to all!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.