How to get Excel to automatically re-apply a filter when my table changes?

[Ian_Heath]

I filter the rows that are greater than 1% in the INFECTIOUS column.  However, this filter is not reapplied when my data changes.  How can I get it to reapply the filter automatically?

[#2]

@Ian_Heath 

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture.

Knowledge of Excel version and the operating system is a must have if you want to proposing a reasonable solution (Example: office version e.g. 2016 or 2019 or 365 web or 365 pro, etc) and your operating system (e.g. Win10 (2004), Win 10 (1903), Mac, etc.).

Nevertheless, here is some general information about it.

Reapply a filter and sort, or clear a filter

https://support.microsoft.com/en-us/office/reapply-a-filter-and-sort-or-clear-a-filter-a46f7534-ce5c...

Thank you for your understanding and patience

Hope I was able to help you.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here

[#3]

Thanks for the reference Nikolino. That helped me to discover that Ctl-Alt-L reapplies the filter. Which is an improvement. However, Ctl-Alt-L needs to applied on the Worksheet which contains the filter, whereas I need to do this on another Worksheet that contains the Chart affected by the parameters I change there. In addition, this is meant for use by the general scientific community without instruction to apply such tricks. What I really need is the filter to be reapplied automatically when the users change the parameters that change the filtered data.

Is there any way of reapplying a filter automatically?

Ian

[#4]

@Ian_Heath 

Apart from what with VBA in the style as you described it, I can't think of anything.

Private Sub Worksheet_Activate ()
     Selection.AutoFilter Field: = 1, Criteria1: = "> 0", Operator: = xlOr, _
         Criteria2: = "<> 0"
End Sub

You must insert the macro in the VBA editor under the table.

The filter is then always updated when you activate the table.

You may have to adjust the value for Filter: = 1 if the automatic filter should / can be set for several columns in the table.

The drop-down arrows are then counted from left to right up to column C.

You then have to use this value instead of 1. 

Knowing the Excel version and operating system would also be an advantage !!!

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#5]

@NikolinoDE  

I use on ordinary filter (not a table filter) on just one column. So the VBA may be fairly simple. However, I'm not clear what activates the filter. I want it to be activated whenever data in that column is updated. I don't really understand how your VBA works.

One other thing, I have always shy-ed away from VBA macros as they are a security risk which will put off most users.

I use excel 2019 on Windows 10 and would dearly love to be to put it online to the public - but I haven't figured how to do this yet.

I would gladly send you my workbook but it is difficult to understand without the accompanying research paper, which I have yet to write. It's an improvement on the standard https://en.wikipedia.org/wiki/Kermack%E2%80%93McKendrick_theory   SIR model, to make it spatial-and-temporal-sensitive, based on the results of my ABM-modeling.

Ian

[#6 · Accepted solution]

@Ian_Heath 

Perhaps more simple variant as here How To Automatically Reapply Auto-Filter When Data Changes In Excel? works in your case. 

[#7]

@SergeiBaklan 

Terrific Sergei.  You are a star.  Does exactly what I wanted.

I have a question though.  I have always shied away from using VBA macros as they seem to be a security risk and other users of my workbook will be put off from accessing it.  Is that a problem with this as well?

Many, many thanks, Ian

[#8]

@Ian_Heath 

Ian, you are welcome.

In this case that's quite simple code which you enter yourselves, thus that's safe. However, if you share workbook, other people could refuse to work with macro-enabled workbook. In organization ITS could test the workbook and give okay to use it. Or ITS won't to waste time testing each macro users bring, in such case they simply prohibit to use any macro. Or ITS trusts to protecting software installed within organization, they have regular backups of everything and allow users to use practically everything.

Approximately the same with individuals. I don't know how to prove them this or that macro-enabled file is safe. Security is always in conflict with usability, the only question is how to find the compromise.

[#9]

@SergeiBaklan 

Sergei, once again, many thanks for your response which I read with great interest.

Unfortunately, it confirmed my fears.  My situation is that I am lone researcher developing a new mathematical model of virus transmission which I plan to publish.  In my research paper I hope to provide a link to a public online version of my spreadsheet so that readers can see live plots of the transmission dynamics according to the parameters they select, which would require the filter to be re-applied.  However, readers will not open a spreadsheet that is flagged as a security risk.  Please can you suggest a method of doing this that would avoid it being a security risk.  Frankly, I cannot see how an online spreadsheet can be a security risk but I admit to being very ignorant about online spreadsheets.

[#10]

@SergeiBaklan 

Thank you so much for this. Can I make it work in more than 1 tab per file? Or in all of them?

[#11]

@SergeiBaklan Is there away to make this work if it is the result of a formula which changes rather than manually changing the cell data?

[#12]

@gracegebbie 

That's quite old post. These date perhaps it's better to use FILTER function - Microsoft Support