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

11 Replies

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 15, 2020

    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-4e20-ac9b-e35eec1c48c0?ui=en-us&rs=en-us&ad=us

     

    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

     

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

Resources