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.
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
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:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!