Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to automatically reapply auto-filter when data changes in Excel?

In Excel, when you apply the Filter function to filter data, the filter result will not be changed automatically with the data changes in your filtered data. For example, when I filter all Apples from the data, now, I change one of the filtered data to BBBBBB, but the result will not be changed as well as following screenshot shown. This article, I will talk about how to reapply auto-filter automatically when data changes in Excel.

doc auot refresh filter 1

Automatically reapply auto-filter when data changes with VBA code


arrow blue right bubble Automatically reapply auto-filter when data changes with VBA code


Normally, you can refresh the filter data by clicking the Reapply feature manually, but, here, I will introduce a VBA code for you to refresh the filter data automatically when data changes, please do as follows:

1. Go to the worksheet that you want to auto refresh filter when data changes.

2. Right click the sheet tab, and select View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module window, see screenshot:

VBA code: Auto reapply filter when data changes:

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

doc auot refresh filter 2

Note: In above code, Sheet 3 is the name of the sheet with auto-filter you use, please change it to your need.

3. And then save and close this code window, now, when you change the filtered data, the Filter function will be auto refreshed at once, see screenshot:

doc auot refresh filter 3


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  • Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  • Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  • No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  • Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    David · 1 months ago
    Hi,

    This code works great, thanks a lot.

    I, however, have one small issue with it - if I change values in any cell that is not part of the table, I am presented with Runtime error saying:

    "Run-time error '91':

    Object variable or With block variable not set up"


    I have options to Debug or End, option to Continue is greyed out. I can click on "End" and the code still works, however it is very annoying having to deal with this popup window after every change.

    Anybody has similar experience or a suggestion about how to sort this?

    Thanks!
    • To post as a guest, your comment is unpublished.
      skyyang · 27 days ago
      Hello, David,
      To solve your problem, you may apply the following code:

      Private Sub Worksheet_Change(ByVal Target As Range)
      On Error Resume Next
      Sheets("Sheet3").AutoFilter.ApplyFilter
      End Sub

      Please try it, hope it can help you!
      • To post as a guest, your comment is unpublished.
        David · 21 days ago
        Hi Skyyang,


        I have implemented your solution and it is indeed fixed.

        Thanks a lot!
  • To post as a guest, your comment is unpublished.
    joe · 2 months ago
    Brilliant and simple to do. Thanks so much!
  • To post as a guest, your comment is unpublished.
    Puly · 4 months ago
    This does not work with filter based on list selection https://www.extendoffice.com/documents/excel/4113-excel-filter-based-on-list-selection.html
  • To post as a guest, your comment is unpublished.
    Rizqi · 7 months ago
    terima Kasih

    sangat membantu
  • To post as a guest, your comment is unpublished.
    Tom · 10 months ago
    Hi, this seems to work great but I am having problems when there are more than one filter on the same worksheet (tab). I converted the range of cells to a table to allow separate and multiple filters within the same worksheet. This example only appears to update one of the tables/filters. Any suggestions on how to update ALL tables/filters within a worksheet?

    Many thanks,

    Tom
    • To post as a guest, your comment is unpublished.
      skyyang · 10 months ago
      Hi, Tom,
      The code in this article works well for multiple tables within a worksheet, you just need to press Enter key after changing the data instead of click to other cell.
      Please try it.