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.
see there: