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
Platinum 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.