How to update advanced filter result automatically in Excel?
When we apply the Advanced Filter feature, we will find that the filtered result will not change automatically with the criteria change. This needs us to apply the Advanced Filter function once again to get the new result. But, do you have any good and quick trick for updating the advanced filter result automatically as following screenshot shown in Excel?
The following VBA code may help you to automatically update the advanced filter result when you change the criteria as you need, please do as this:
1. Right click the sheet tab that you want to filter the data automatically, and then choose View Code from the context menu, in the opened Microsoft Visual Basic for applications window, copy and paste the following code into the blank Module:
VBA code: Update advanced filter result automatically:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A5:D21").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _ ("A1:C3"), Unique:=False End Sub
Note: In the above code: A5:D21 is the data range which you want to filter, A1:C3 is the criteria range to filter based on.
2. Then save and close the code window, now, when you change the criteria in the criteria range, the filtered result will be updated automatically at once.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 4 months agoThis is likely not relevant anymore. Office excel now has a FILTER function that auto updates from what I gather, making advanced filter a sort of weak pivot table. FILTER function is what should be used moving forward? Sounds like excel's solution to the issue you state above not requring VBA to do.
- To post as a guest, your comment is unpublished.· 9 months agoHi, I am applying advanced filter for a single column for unique values(with no criteria) . I need it to be updated by itself.
Could you please help me with that?
- To post as a guest, your comment is unpublished.· 1 years agoIs there a way to do this without using VBA?
- To post as a guest, your comment is unpublished.· 1 years agoHello, this code works perfectly, there is just one problem, the copy/paste fonction doesn't work anymore.
How could it be fixed?
Thanks in advance
- To post as a guest, your comment is unpublished.· 1 years ago@Hannah: Yes, it is possible.
In the following code the criteria is on the sheet "Filter".
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A5:D23").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
- To post as a guest, your comment is unpublished.· 1 years agoCe code donne une erreur 1004 référence non valide. pouvez vous corriger le code proposé?
- To post as a guest, your comment is unpublished.· 1 years agoBonjour, merci pour votre post qui m'aide énormément. Toutefois quel serait le code à ajouter pour coller le résultat dans un tableau d'une autre feuille de fichier et si possible pour au passage ne coller que certaines colonnes ?
Pour être plus précise, en utilisant votre exemple je voudrais copier uniquement les colonnes "Product" et "Name" du résultat du filtre et ce dans une nouvelle feuille. Merci.
- To post as a guest, your comment is unpublished.· 2 years agoCan I make the criteria come from a second sheet?
- To post as a guest, your comment is unpublished.· 2 years agoit refreshes every time when any data changes.
is it possible to refresh only when criteria changes.