access calc autofilter options from macro - expert needed

hello @all,

once you click the dropdown arrow in the top row of a table / range filtered with autofilter, you get a select-box / select-list showing the options available as filter criteria for that column (not! all values appearing in that column, but only the values appearing in those rows which match the search criteria in other filtered columns). that’s quite handy!

short:

  • does anyone know where to find that list in the data structures accessible by a macro?

or:

  • does anyone know a simple ‘one-click’ method to step the filter through the options?

long:

i manage searching and handling big data tables with macros which e.g. set the autofilter to the value of the actual cell, or switch it off for that column. for some checks / evaluations it would be very! very! handy to ‘step’ through the items with one click or keystroke, rather then either:

  • loop
  • ‘find the top line’,
  • ‘click the selection arrow’,
  • ‘find the selected item’,
  • (if necc. use the slider to see items not in the upper part of the list)’,
  • ‘deselect the actual selected item’,
  • ‘select the next item’,
  • ‘click ok’,
  • repeat

or

  • loop
  • ‘remember the actual selected item’,
  • ‘deactivate filtering for that column (macro and keyboard shortcut to activate exist)’,
  • ‘find the next possible value by manual scrolling (easy if numerical, if next increment exists, and if present in a row near the actual selection (e.g. if the data is sorted for that column), difficult if alphanumeric, next increment not in the possibilities, and present ‘somewhere else’ in the table (esp. when the table is not sorted acc. that column, what applies for most cases)’,
  • ‘activate that cell (by clicking in it)’,
  • ‘activate filtering acc. the value in that cell (macro and keyboard shortcut for that task exist)’,
  • repeat

both methods work and produce the appropriate output, but they are ‘driving me nuts’ and waste my time with the ‘point-and-click-o-mania’.

to describe my actual task: the sheet contains plenty lines with income and expense lines for plenty projects, randomly spread in the table (sorted by the date of booking), the header area of the table calculates the profit of all ‘shown’ lines. the projects should be checked for app. profit, resp. against missing bookings. i select one project, the top area calculates it’s profit, if ok i switch to the next project, if not i check where the fault is. for 20 projects a work of minutes, for 2.000 a work of two days with the click-o-mania-methods described above (and killing my fingers, mouse, keyboard … what actually happened).

i think / hope the work can boil down to thirty minutes with a macro which steps through the available values.

  • loop
  • ‘click to activate next selection (fingers / mouse may rest where they are for the next click)’,
  • 'observe calculated result (eye maý rest on that cell for the next check),
  • repeat

(of course it would be nice to have an option to s elect the previous item too)

it is not an option to sort the list acc. the column i’m working in, it’s not a simple list but a financial table with ‘stacked calculations’ which would break on re-sorting,

either way:

  • somebody has a hint where to find / access the requested data and i ‘pimp up’ my macros,

  • somebody knows another easy way to step through the autofilter criteria for a column,

would help me very much, and thus be highly appreciated.

(hints like ‘use a database for tasks like that’, ‘find a financial program suiting your needs’ are ‘less welcome’, as both would ‘pull away’ the data from my eyes, would shrink the control to the functionality built into the program or programmed for the database, and reduce my flexibility to alter data structures, evaluate whatever i want and so on. also it would be a ‘big-step’ requiring system analysis, check of possible programs, conversion expenses, change the way to work i’m used to …)

what can i give back to the community? the work on the macro, and i think / hope this powerful way to handle quite big data may be handy for others too, once they get to know about it. (may be in far future LO will implement something like this in the program).

thanks for reading so far,

tia for any help!!!,

reg.

b.

Sound funny and interesting. I can try help you, but only with macros Python, Basic it’s so slow and boring.

@mauricio, would be great, afaik no problem to use macros in different languages together, i looked into phyton once and saw it similar complicated as LO basic, thus my knowledge is still limited to that …

if you manage: ‘table, two columns, both filtered with autofilter, advance the filter criterium for one column from the actual to the next possible value’ i can do the rest.

This question: How to switch AutoFilter to the next line with a macro in LibreOffice Calc? (understandable only with the attached picture) touches my wishes, but the answer there doesn’t give me much help …

something about the work up to now you may find in:

and:

If I’ve understood correctly, you need change filter in column, by one by? like next and previous button?

hi @all,

got a solution, :slight_smile: see there:

Filterung einer Tabelle durch die Werte ‘durchsteppen’

(german forum, german language),

may be improvements are possible … ??? … but already more than a proof of concept, it is! suitable for productive use.

try: importing the macro, assign it to a keyboard shortcut (alt-F3), open one of your tables, verify to have a ‘database range’ defined (data - define range), and see how fast you can step through the filterings in that range with the shortcut …

if anybody knows a better solution … i’d be happy to hear about it, for the moment i consider this tool and ‘datasurfer’ the fastest and most flexible ways (may i say most powerful ways?) to explore, check, and navigate within your data.

b.

Maybe?

image description

with a video - ‘nice’ - yes, the functionlity in col. B is what i need, but your video doesn’t show how it’s achieved / how it works … ?