Ask Your Question

access calc autofilter options from macro - expert needed

asked 2019-11-14 13:36:26 +0200

newbie-02 gravatar image

updated 2020-07-20 10:14:06 +0200

Alex Kemp gravatar image

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!


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


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


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


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

edit retag flag offensive close merge delete


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

mauricio gravatar imagemauricio ( 2019-11-14 19:00:28 +0200 )edit

@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: (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:


newbie-02 gravatar imagenewbie-02 ( 2019-11-14 22:03:47 +0200 )edit

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

mauricio gravatar imagemauricio ( 2019-11-18 16:27:08 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-11-17 10:59:32 +0200

newbie-02 gravatar image

updated 2019-11-17 18:15:52 +0200

hi @all,

got a solution, :-) 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.


edit flag offensive delete link more

answered 2019-11-18 17:05:24 +0200


image description

edit flag offensive delete link more


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

newbie-02 gravatar imagenewbie-02 ( 2019-11-20 18:07:57 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-11-14 13:36:26 +0200

Seen: 278 times

Last updated: Nov 18 '19