Forum Discussion

Ian_Heath's avatar
Ian_Heath
Copper Contributor
Nov 14, 2020
Solved

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

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?
excel
Ian_Heath's avatar
Ian_Heath
Copper Contributor
Nov 15, 2020
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

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Nov 15, 2020

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.

  • Ian_Heath's avatar
    Ian_Heath
    Copper Contributor
    Nov 15, 2020

    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

Resources