Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

macro to modify autofilter in calc

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

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