macro to modify autofilter in calc

English
, , , , , ,
#1

edit:

hello all,

can anyone help to make the macro ‘modifying’ the filtering criteria rather than setting a new one?

i got the macro from librebel, and found some others in the web which use autofilter, all set the filter criteria for the entire table ‘from scratch’ - and this way as ‘not filtered’ except the one column where they are working on.

excel with one simple line:

1: ActiveSheet.Range(“data1”).AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Text

does a ‘modify’, the filter criteria for all other columns stay as they are, only for the actual column the criteria is set.
(or changed if there was one before (that’s not! a silly idea, one could think in an already filtered column the macro would only change A to A, think of manually retyping a filtered A to B and then call the macro, you’ll see all rows with B in that column, nice quick help in some cases.))

the excel behaviour is much more ‘intuitive’, and very flexible for daily work in large tables … i’m sure it is! possible in LO too, but how?

three other enhancements if someone has time?:

  • the filtering for a date value formatted as YY-MM-DD should result in something reasonable,
  • the filtering for any date string should avoid the hierarchical selection-click-mania in the selector drop-down-box, just a simple list of values would do better,
  • the filtering for special formatted cells should / could work better, LO has difficulties as soon as i format a week-number as '01’raher than ‘1’, but that’s useful for sorting whenever the number is interpreted as, or included in, a string.

reg.

newbie-01

/edit

hello all,

i’m new to this Forum, curious whether it’ll work, and appreciate any help.

i’m trying to move from Excel to lo, installed Version 5.4.5.1 x64 english on win 10, with a jre, 64 bit, 9.04 or 9.4 or similar,

i’m used to work and do ‘data mining’ in large spreadsheets, where my favorite tools are two nice Little macros which:

1: applies an autofilter to the actual column with the value of the actual cell,

2: sets the actual column to unfiltered.

in Excel VBA these are ‘one-liners’,

1: ActiveSheet.Range(“data1”).AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Text

2: ActiveSheet.Range(“data1”).AutoFilter Field:=ActiveCell.Column

and the only prerequisite is: a range ‘data1’ defined in the sheet.

These macros activated by a key-combination-shortcut enable very fast and handy orientation in my spreadsheets, especially finding datasets combined to the actual line by any item in it.

i’m used to have and need it working ‘fast on your fingertips’, any ‘mousing’, ‘clicking’, selection boxes or other things like remembering the value of the cell and then finding them in the dropdown list of the filter header are a no-no! … they can be done but are excessive time consuming and - as seen in Excel - unneccessary,

can anyone build something similar in lo?

i googled around for some hours, and found some examples in Basic all with some shortages,

they are Long and difficult to adapt or to manipulate without programming experience in lo,

they rely on ‘filter’ instead of ‘auto-filter’,

!!! they kill the filter criteria if there are any set in any other columns, !!!

they have difficulties in columns with Special values, e.g. Dates, they try to install the funny hirarchical year-month-day checkboxes what’s not working and quite unneccesary for me,

if i had to work forward from what i have, i’d need to ‘read’ he filter set for a range, manipulate one of the fields in it, and write it back as the new filter criteria … i think i can achive this with a lot of trial and error, just i’m short in time.

and i’d like an elegant short solution similar to the Excel ones, maybee this can help plenty People with lots of data in large tables … ???

one last wish, Excel does! preserve the actual filtering state over close and reopen of the table, lo doesn’t, it’s not the first Preference, but if possible would be ‘nice to have’ …

thanks for any help and hints,

newbie-01

macro to !modify! autofilter in calc II [solved]
access calc autofilter options from macro - expert needed
Een macro opnemen
#2

Hello @newbie-01, and welcome to LibreOffice :slight_smile:

Please confirm that you really mean the AutoFilter function in Calc, that can be toggled On/Off via the menu “Data : AutoFilter”.

From your description it seems that you rather would have a macro to automatically Set/Remove a Standard Filter.

The difference between AutoFilter and Standard FIlter is described here

#3

hello librebel,

thks for your welcome, comment and question,

the target is the functionality, - it’s great -, it doesn’t matter how it’s implemented.

(i’m more a user than a coder, so i’m open for every good suggestion. my approach was from Excel and i found a nice solution with autofilter quite fast, thus on migratimg to lo my first thought was ‘autofilter’. i’ve learned that lo has even more filter functions, but at the Moment i only need functionality)

reg.

newbie-0

#4

There is a followup question at macro to !modify! autofilter in calc II [solved].

#5

Having no experience in Excel, i hope i interpreted your intention correctly … Please try if the following method provides the functionality that you want:

To set the filter, you could call:

Calc_filterActiveCell( "data1” )

To remove the filter, you could call:

Calc_filterActiveCell( "data1”, -1 )

Code:

Sub Calc_filterActiveCell( sRangeName As String, Optional lOperator, Optional bIsNumeric )
REM Filter Rows in the specified CellRange, based on a single filter criterium.
REM The filter criterium is determined by the currently Active Cell, whose cell value should be related (via the specified FilterOperator) to the values in the active Column.
REM NB. This requires that the currently active Cell is located within the specified CellRange.
REM <sRangeName> : The Name of a Named Range, or a Range Address String.
REM <lOperator>  : [OPTIONAL] Member of Constant Group com.sun.star.sheet.FilterOperator2 determining the criterium operator; Default=2 (EQUAL).
REM                0=EMPTY; 1=NOT_EMPTY;
REM                2=EQUAL; 3=NOT_EQUAL; 12=CONTAINS; 13=DOES_NOT_CONTAIN; 14=BEGINS_WITH; 15=DOES_NOT_BEGIN_WITH; 16=ENDS_WITH; 17=DOES_NOT_END_WITH.
REM                4=GREATER; 5=GREATER_EQUAL; 6=LESS; 7=LESS_EQUAL; 
REM                8=TOP_VALUES; 9=TOP_PERCENT; 10=BOTTOM_VALUES; 11=BOTTOM_PERCENT.
REM                You can pass -1 here to remove the Filter again.
REM <bIsNumeric> : [OPTIONAL] Boolean indicating if the Filter Field is Numeric (True) or AlphaNumeric (False); Default=Active Cell Type.
	Dim oDoc As Object     : oDoc = ThisComponent		REM Must be a Calc document..
	If Not oDoc.supportsService( "com.sun.star.sheet.SpreadsheetDocument" ) Then Stop
	
	Dim oSheet As Object   : oSheet = oDoc.CurrentController.ActiveSheet	
	Dim oRange As Object   : oRange = oSheet.getCellRangeByName( sRangeName )
	
	Dim oSelection As Object : oSelection = oDoc.getCurrentSelection()
	If Not oSelection.supportsService( "com.sun.star.sheet.SheetCell" ) Then Stop
	
	Dim oAddress As Object : oAddress = oSelection.getCellAddress()
	If oAddress.Column < oRange.RangeAddress.StartColumn Or _
	   oAddress.Column > oRange.RangeAddress.EndColumn   Or _
	   oAddress.Row    < oRange.RangeAddress.StartRow    Or _
	   oAddress.Row    > oRange.RangeAddress.EndRow      Then Stop	REM Active cell is outside of the specified Range: Fail quietly.
	
	Dim oActiveCell As Object : oActiveCell = oSheet.getCellByPosition( oAddress.Column, oAddress.Row )
	
	If IsMissing( lOperator )  Then lOperator  = com.sun.star.sheet.FilterOperator2.EQUAL
	If IsMissing( bIsNumeric ) Then bIsNumeric = ( oActiveCell.getType() <> com.sun.star.table.CellContentType.TEXT )
	
	Dim oFilterField(0) As New com.sun.star.sheet.TableFilterField2
	oFilterField(0).Field  = oAddress.Column - oRange.RangeAddress.StartColumn
	If lOperator >= 0 And lOperator <= 17 Then
		oFilterField(0).Operator     = lOperator
		oFilterField(0).IsNumeric    = bIsNumeric
		oFilterField(0).StringValue  = oActiveCell.getString()	REM Set both String and Value,
		oFilterField(0).NumericValue = oActiveCell.getValue()	REM IsNumeric determines whether NumericValue or StringValue is used.
	End If
	
    Dim oFilter As Object  : oFilter  = oRange.createFilterDescriptor( True )	REM create Empty Filter.                        
	oFilter.UseRegularExpressions = False					REM set your desired Filter Settings here...
	oFilter.IsCaseSensitive = False
	oFilter.ContainsHeader  = False
	oFilter.SkipDuplicates  = False
	oFilter.CopyOutputData  = False
	oFilter.Orientation = com.sun.star.table.TableOrientation.ROWS
	oFilter.setFilterFields2( oFilterField )
	
	oRange.filter( oFilter )
End Sub

Based on a few assumptions, please report back if any adjustments are needed,

With regards, lib

#6

hello librebel,
as i’m only allowed one answer i’d change the question instead, your macro works, but, would be much better if it modifies the filter rather than replacing it …
thanks,
newbie-01

#7

hello all,

can anyone help to make the macro ‘modifying’ the filtering criteria rather than setting a new one?

i got the macro from librebel, and found some others in the web which use autofilter, all set the filter criteria for the entire table ‘from scratch’ - and this way as ‘not filtered’ except the one column where they are working on.

excel with one simple line:

1: ActiveSheet.Range(“data1”).AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Text

does a ‘modify’, the filter criteria for all other columns stay as they are, only for the actual column the criteria is set.
(or changed if there was one before (that’ not! a silly idea, one could think in an already filtered column the macro would only change A to A, think of manually retyping a filtered A to B and then call the macro, you’ll see all rows with B in that column, nice quick help in some cases.))

the excel behaviour is much more ‘intuitive’, and very flexible for daily work in large tables … i’m sure it is! possible in LO too, but how?

three other enhancements if someone has time?:

  • the filtering for a date value formatted as YY-MM-DD should result in something reasonable,
  • the filtering for any date string should avoid the hierarchical selection-click-mania in the selector drop-down-box, just a simple list of values would do better,
  • the filtering for special formatted cells schould / could work better, LO has difficulties as soon as i format a week-number as '01’raher than ‘1’, but thats useful for sorting whenever the number is interpreted as, or included in, a string.

reg.

newbie-01

old, solved, the macro had to be called with a parameter set:

hello librebel,

many hanks for your work and effort … but … sadly,

the result is

A Scripting Framework error occurred while running the Basic script Standard.Module1.Calc_filterActiveCell.

Message: wrong number of parameters!

debugging attempts by stepping through with F8 result in:

BASIC runtime error.
‘1’

Type: com.sun.star.uno.RuntimeException
Message:

in the line 24
Dim oRange As Object : oRange = oSheet.getCellRangeByName( sRangeName )

regardless whether i replace sRangeName by a defined and named range in the sheet.

(solved - just lines before ‘—…’ are formatted as bold, add a blank line to avoid; sorry, i don’t know how to format the message lines as normal text)

some thoughts:

‘autofilter’ would be ‘nicer’ than ‘filter’ because it gives more options for dummies - like me - to go onward from the filtered list,

the one-liner VBAProject Makros, which i ‘listed’ in the first post, do! work in lo with two impacting shortcomings:

  • filter arguments set for other columns are ‘wiped-out’ by the macro,
    (imagine the list is filtered for one person, when i add filtering for a special week i get this week !complete with all persons! thats much better in excel where the other filters are preserved. i’m sure it’s possible in lo too, but don’t now how)
    (i’ve read somewhere that the ‘language’ in lo and excel are the same, but the data-structures differ, maybe it’s only necessary to adapt the vba ‘command’ to the lo data structures)

  • filtering in columns with date values results in nonsense, the hierarchical ‘+’-signed structure in the filter header is activated but no lines are selected … :-
    (it works with dates formatted as numbers but that’s difficult for humans, wrong results with DD-MM-YYYY (right line shown but nonsense in the selection box), and nonsense - none selected - with YY-MM-DD, (i’d prefer YY-MM-DD working), and! i’d prefer to get rid of this incredible unhandy selection procedure in the selection box with the ‘select year, select month, select day’ bullshit)

regards,

newbie-01

#8

Hello @newbie-01,

It appears that you attempted to run the macro without passing the required parameter.
According to the instructions that i gave above in my answer, the macro must be called with at least the RangeName parameter being specified ( that is “data1” in your case).
Else it will result in the error that you encountered.

To run a macro with specified parameters, you could enclose it within another method, that has no parameters, e.g.:

[continued in next comment…]

#9

e.g.:

Sub Calc_filter_data1()
	Calc_filterActiveCell( "data1” )
End Sub

Then assign your keyboard shortcut to that.

#10

hi, thanks, that works! (your quotation marks are ‘wrong’)
sorry for being so … knowledgeless? …
still one elementary shortcoming!
excel with the line i ‘coded’ does modify! the filtering structure and preserves the filter conditions for other columns. your attempt - and some others i found in the web - do a ‘renew’ of the filter … a modify would be much more ‘handy’ for my work.
i tried to understand the structures and methods … it’s all buried under so many text … can you help?

#11

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