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.