Ask Your Question
1

macro to modify autofilter in calc

asked 2018-03-17 23:36:00 +0200

newbie-01 gravatar image

updated 2018-03-28 02:19:44 +0200

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 ... (more)

edit retag flag offensive close merge delete

Comments

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

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

librebel gravatar imagelibrebel ( 2018-03-18 01:29:55 +0200 )edit

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

newbie-01 gravatar imagenewbie-01 ( 2018-03-18 10:07:49 +0200 )edit

There is a followup question at https://ask.libreoffice.org/en/questi....

Jim K gravatar imageJim K ( 2018-04-04 23:33:11 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2018-03-25 20:46:57 +0200

newbie-01 gravatar image

updated 2018-03-28 02:16:53 +0200

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 ...

(more)
edit flag offensive delete link more

Comments

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...]

librebel gravatar imagelibrebel ( 2018-03-26 03:54:06 +0200 )edit

e.g.:

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

Then assign your keyboard shortcut to that.

librebel gravatar imagelibrebel ( 2018-03-26 03:55:11 +0200 )edit

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?

newbie-01 gravatar imagenewbie-01 ( 2018-03-28 01:30:29 +0200 )edit
0

answered 2018-03-20 03:57:59 +0200

librebel gravatar image

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

edit flag offensive delete link more

Comments

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

newbie-01 gravatar imagenewbie-01 ( 2018-04-02 17:22:54 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-03-17 23:36:00 +0200

Seen: 603 times

Last updated: Mar 28 '18