Skip to main content

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

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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

kte tab 201905

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!
Comments (38)
Rated 5 out of 5 · 1 ratings
Larry
Larry
This comment was minimized by the moderator on the site
Its not working if able changing by itslef, like when you linking data from another app, can someone help me fix this please
Reply
0
0
John Edwards
John Edwards
This comment was minimized by the moderator on the site
Pasted it as shown, but not working. The sheet I need updated is an invoice that has formulas that are pulling from another sheet. Since the blank cells all have formulas in them, is that preventing it from working? Seems simple for it to work, so I'm getting frustrated with myself.
Reply
0
0
Sam Clarke
Sam Clarke
This comment was minimized by the moderator on the site
2022 update!
Just add this to the sheets you want it applied too.

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFilter.ApplyFilter
End Sub
Reply
0
0
Rated 5 out of 5
Hetfield1985
Hetfield1985
This comment was minimized by the moderator on the site
My input data is on the first sheet of the book, and the table in which the filter is applied is on another sheet of the same book. When I change the data on the first sheet, the filter on the second sheet should be updated. But if at the time of changing the data, a cell in the range of this table is not selected, then I get an error:"Run-time error '91': Object variable or With block variable not set". Therefore, i am should always check that the cursor is in a table range cell and not in any other place on the sheet. How to fix it?Solved:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("name of sheet").ListObjects("name of table").AutoFilter.ApplyFilter
End Sub
Reply
Report
0
0
Kay
Kay
This comment was minimized by the moderator on the site
Thank you. Works great!
Reply
0
0
Dom
Dom
This comment was minimized by the moderator on the site
Hey this works great but I'd like to re-apply the autofilter to multiple sheets if possible.
I have 3 sheets (Enquiry, Booked, No sale)

What I'm trying to do is essentially move the data when I change the item status code:
"Enquiry" is filtered to show 'In progress' only, "Booked" is filtered to show 'Booked' only etc

Is there a way to re-apply filter to multiple worksheets when making a change on "enquiry"
Reply
0
0
Danny
Danny
This comment was minimized by the moderator on the site
I actually have data from an other Excel file that got imported in a Excelsheet with the name "Database". Then I import this data in the same Excel file but in an other ExcelSheet "Overview". I want when the data changes in the orgininal source, that the filter applies in the sheet "Overview". Thank you in forward for the one who can help me :). P.S. cant use VBA in the firt excelsheet
Reply
0
0
Anthony
Anthony
This comment was minimized by the moderator on the site
Hi,

This is a great bit of code thank you. The only issue I am having is I'm using a drop down on a separate chart sheet. If I manually change the value in the cell associated with the drop down, it works. But when I try to just use the drop down, it won't update. Any thoughts?
Reply
0
0
Jetson
Jetson
This comment was minimized by the moderator on the site
Hi, thanks so much for the help. Something isn't working right for me. Here's the story.

Sheet1 has variable data. Sheet3 has static data and filter. Filter criteria on "Sheet3" comes from Sheet1. Sheet1 has data that comes from filtered results on Sheet3.

Sheet3 has code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1:U14").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A22:U23"), CopyToRange:=Range("A25:U26"), Unique:=False
End Sub

It works great if I do anything on Sheet3. No problems. Thank you!

At first I had code on Sheet1:

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

Which resulted in the error "Runtime error 91, Object Variable or With Block not Set".

I changed the code based on comments to be:

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

Now I don't get an error, but the data on Sheet3 and therefore Sheet1 don't change. In other words, the event of applying the filter to Sheet3 doesn't occur when I make a change on Sheet1. It doesn't matter if I hit <return> or click on another cell after changing the Sheet3 filter criteria cell that is set on Sheet1.

As an aside, I expect that if I wanted to have multiple cells on Sheet1 that caused filters on Sheets 4 and 5 in addition to Sheet3, I would need the code on Sheet 1 to read:

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

Thanks again!
Reply
Report
0
0
Neil
Neil
This comment was minimized by the moderator on the site
Cant get this to work at all on office 365
any suggestions
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations