[Solved] Macro to enable auto refresh of advanced filter

[Msasso]

Hi all,

First time posting here so apologies if i haven't followed any rules/etiquette!

Essentially what i am trying to do is hide rows based on a given cells value (todays date), i am able to do this manually by selecting the range (A25:F61) then advanced filter (A21:F22). However what i would like is that the date changes automatically using today() then the filter reapply's itself so that only today's information is displayed. I attempted to record a macro of this but it wouldn't work, i have no grasp of macros, i have even been looking at other examples online and attempting to modify them for my sheet but to no avail, would someone be kind enough to write one for me?

Kind Regards
Marco

[Villeroy]

ThisComponent.DatabaseRanges.getByName("your_db_range_name").refresh() sorry, will not read new values form cells.

Look at this: download/file.php?id=136

[Villeroy]

Code: Select all

ThisComponent.DatabaseRanges.getByName("your_db_range_name").refresh()

should work since this action also recalculates volatile functions such as TODAY or NOW.

menu:Data>Refresh does the trick without macro.

[Msasso]

Hi Villeroy,

I am looking at the first post you sent, would prefer it to be entirely automated as i want to make it as fool proof as possible (End users will vary in levels of competency) I have followed the instructions step by step but this message appears upon entering anything in the text box;



I'm not sure what it means or what i have done wrong.

Thank you for your help so far

[Villeroy]

There is no database range named "Import1". What is the name of your database range?
menu:Data>Define...
Define a database range, delete all macro bullshit and call menu:Data>Refresh once a day to update your date filter.

[Msasso]

Hey,

Defined the range, labelled it as data, now the following message is appearing



I appreciate what you are saying about updating once daily manually, but that is not what i want, it has to be automated, please help

[Villeroy]

There is no named range named "criteria".

Delete all macro bullshit and call menu:Data>Refresh once a day to update your date filter.

[Msasso]

There is a range named criteria,



Your proposed second solution doesn't meet my criteria, why should i have to settle if what i would like is possible. I really, really appreciate your help as i cannot do this on my own but please meet the conditions!

[Villeroy]

Do not mimic things you do not understand. If you want to filter by today's date, put =TODAY() in the criteria cell (no regular expression required), call Data>Filter>Advanced, select the criteria range. When the next day has come, click any cell in the database range and call Data>Refresh

[Msasso]

Thank you for the manual solution, i tried it and it does work well.

I realize what i was doing wrong, and named the criteria range, now this is the error;



Please don't give up on me!

[Villeroy]

P.S. If your dates happen to be strings (don't know why Excel users do that), the formula for the criterion would be =TEXT(TODAY();"DD/MM/YY"))

I realize what i was doing wrong, and named the criteria range, now this is the error;

You have zero programming skills. Stop using macros.

[Msasso]

You are correct, i have zero programming skills.
I do however need this to be automatic not manual via refreshing the data so what else should i do?
I am here politely asking for someone to walk me through this, if that is not you then that's fine, thank you for your time.