macro script "getFilterDescriptor()" broken on fresh loaded files?

hi @all,

i have a little issue with a function? method? in a macro and ask other users to check it out before i ‘file a bug’ because of it,

in short: “getFilterDescriptor()” gets wrong values for ‘field’ as property of ‘filterfields(x)’ in the ‘filterfields’ part of the struct, once two conditions are met:

  • in a fresh loaded file where filters are set,
  • if the ‘defined range’ it works in has an ‘offset’ from cell A1,

in long: i have a macro that ‘steps through filtering values’ of the autofilter for a column in order to check subsets of the table quite fast :slight_smile: - see attached code,

it produces erroneous results with the abovementioned conditions,

to check let it run on the different sheets of the attached file,

“getFilterDescriptor()”, in this case called as “oFilterDesc = oRange.getFilterDescriptor()” gets the number of the column as value for “fields” on sheets 1,2,3, and the number of the column reduced by the ‘vertical offset’ (start row) of the ‘defined range’ it’s working in on sheets 4 and 5.

but “referredcells.filter(oFilterDesc)” does other calculations for the column, thus applying it to wrong columns in sheet 2,3,4, sheet 5 looks healthy, but I’m guessing only because two mistakes cancel each other out,

after ‘restart’ of the autofilter - autofilter off - autofilter on - the value for ‘field’ is always calculated relative to the ‘defined range’, and everything works as intended,

file with macro to play with it:


the macro code:

code starts here, inserting it as a block is still somewhat difficult for me:

sub PreviousFilterShortcut

’ this macro - steps back - by one value in the range of possible autofilter
’ values for the actual column. useful to cycle through subranges of datasets
’ and e.g. check their results, e.g. in calculated subtotals of columns,
’ (subtotal(9,(´range´)).

’ the only requirements are that you have a ‘database range’ defined
’ in the table under <data - define range>, that you have selected a single cell,
’ and that this cell, the ‘focus’, is within the defined range,

oDoc     = ThisComponent
oControl = oDoc.CurrentController
oSheet   = oControl.getActiveSheet
oCell    = oDoc.getCurrentSelection
Column  = oCell.CellAddress.Column

' search for database range the focus is in
oDBRanges = oDoc.DatabaseRanges
for i = 0 to oDBRanges.Count-1
    oDBrange   = oDBRanges.getByIndex(i)
    oCellrange = oDbrange.ReferredCells
    if oCellrange.queryIntersection(oCell.RangeAddress).Count > 0 then
        oRange = oDBrange
    End If

' add a messagebox for errors, 
if isempty(oRange) then msgbox "actual cell not within a database range",0,""
if isempty(oRange) then exit sub
' define new filter-description
Dim oNeu As New
With oNeu
 .Field = Column - oRange.ReferredCells.RangeAddress.StartColumn  ' filter-column (other position in code than in datasurfer?) 
 .IsNumeric = False
 .StringValue = ""
 .Operator =
End With	

'analyse existing filter	 
oFilterDesc = oRange.getFilterDescriptor()
Do while sAlt="" and n<ubound(aFields())
	if aFields(n).Field=Column - oRange.ReferredCells.RangeAddress.StartColumn then
		'column is actually filtered
		aFields(n)=oNeu 'filter "off"
	end if

' filter without filtering actual column
oRange.AutoFilter = True

' some presets
if sAlt="" then 
	Redim Preserve aFields(n)
end if

' get all possible values for the actual column, respecting filtering done in other columns, 
zellen=	oRange.ReferredCells.queryVisiblecells.queryintersection(oCell.columns(0).RangeAddress).cells.createenumeration

' search for max of smaller values 

do while zellen.hasmoreelements
	if sTemp>sMax then sMax=sTemp ' find max
	if sNeu="" then 
		if sTemp<sAlt then sNeu=sTemp ' 
		if sTemp<sAlt and sTemp>sNeu then sNeu=sTemp
	end if
if sNeu="" then sNeu=sMax

' set new filter criteria
aFields(n).Operator =

' apply filtering

End Sub 'PreviousFilterShortcut

code end here, inserting it as a block is still somewhat difficult for me,

the macro is one of four which do quite handy things for me, (toggle autofilter on/off, modify autofilter according value of selected cell (datasurfer), step forward through the filtering values in one column, step backwards) which i’d like to give to others and ‘the community’ once they run well,

the last three suffer from mentioned problem, i’d provide ‘backwards’ as example reg. it’s ‘short’ and ‘in english’, ‘forward’ is partly german,

credits to and esp. ‘mikele’ who helped coming so far …

tia for any help

as of now i think it’s resulting from that:

filesave: fileopen: macro: xml: calc filtered ranges wrongly saved in .ods format?