Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Automatisch filter automatisch opnieuw toepassen wanneer gegevens in Excel worden gewijzigd?

In Excel, wanneer u de filters functie om gegevens te filteren, wordt het filterresultaat niet automatisch gewijzigd met de gegevenswijzigingen in uw gefilterde gegevens. Wanneer ik bijvoorbeeld alle appels uit de gegevens filter, verander ik nu een van de gefilterde gegevens in BBBBBB, maar het resultaat zal niet worden gewijzigd, evenals het volgende screenshot. In dit artikel zal ik het hebben over automatisch opnieuw filteren wanneer gegevens in Excel worden gewijzigd.

doc auot refresh filter 1

Automatisch filter automatisch opnieuw toepassen wanneer gegevens worden gewijzigd met VBA-code


pijl blauwe rechterbel Automatisch filter automatisch opnieuw toepassen wanneer gegevens worden gewijzigd met VBA-code


Normaal gesproken kunt u de filtergegevens vernieuwen door handmatig op de functie Opnieuw toepassen te klikken, maar hier zal ik een VBA-code voor u invoeren om de filtergegevens automatisch te vernieuwen wanneer de gegevens worden gewijzigd. Ga hiervoor als volgt te werk:

1. Ga naar het werkblad waarvan u het filter automatisch wilt verversen wanneer gegevens worden gewijzigd.

2. Klik met de rechtermuisknop op het bladentabblad en selecteer Bekijk code uit het contextmenu, in de popped out Microsoft Visual Basic voor toepassingen venster, kopieer en plak de volgende code in het lege module venster, zie screenshot:

VBA-code: filter automatisch opnieuw toepassen wanneer gegevens worden gewijzigd:

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

doc auot refresh filter 2

Notes: In bovenstaande code, 3 Sheet is de naam van het blad met automatisch filter dat u gebruikt, verander het naar uw behoefte.

3. En sla dan op en sluit dit codevenster nu, wanneer u de gefilterde gegevens wijzigt, de filters functie wordt in één keer automatisch ververst, zie screenshot:

doc auot refresh filter 3


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen en gegevens bewaren; Inhoud gesplitste cellen; Combineer dubbele rijen en som / gemiddelde... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • Favoriete en snel formules invoegen, Bereiken, grafieken en afbeeldingen; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Werkt met Office 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in bedrijf. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
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.
    Neil · 2 months ago
    Cant get this to work at all on office 365
    any suggestions
  • To post as a guest, your comment is unpublished.
    David · 4 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 · 3 months 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 · 3 months 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 · 5 months ago
    Brilliant and simple to do. Thanks so much!
  • To post as a guest, your comment is unpublished.
    Puly · 7 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 · 10 months ago
    terima Kasih

    sangat membantu
  • To post as a guest, your comment is unpublished.
    Tom · 1 years 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 · 1 years 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.
  • To post as a guest, your comment is unpublished.
    Alex · 1 years ago
    Hi, that works great, however only when manually changing data in the table.

    I have a ‘top ten/leader board’ style filtered table which is populated from data entry on a separate worksheet (actually the data goes through 3 worksheets before getting to the table). When the data is changed in the data entry worksheet the leader board table figures updates however the filter doesn’t auto refresh.
    Any ideas on how to do that?
    Much Obliged.
    Alex
  • To post as a guest, your comment is unpublished.
    Chris · 1 years ago
    This seems great. Can you tell me how to do the same for Sort, rather than Filter, please?
  • To post as a guest, your comment is unpublished.
    Steve Miller · 1 years ago
    works like a champ, and so simple. thank you very much!
  • To post as a guest, your comment is unpublished.
    Mike T · 1 years ago
    This solution works perfectly. Thanks for writing it up! If anyone is having trouble, there are a few things to consider.

    First, the Worksheet_Change event is called on a sheet-by-sheet basis. This means if you have multiple sheets which have filters you need updated, you will need to respond to all those events. One Worksheet_Change subroutine for each worksheet, not one subroutine for the entire workbook (one exception - see note below).

    Second, and a follow-on to the first, the code must be placed in the code module specific to the worksheet to be monitored. Its easy to (inadvertently) switch code modules once you get into the VB editor, so care must be taken to place it specific to the sheet you want to monitor for data changes.

    Third, this is unconfirmed, but possibly a point of error. The example uses sheet names of "Sheet1", "Sheet2", etc. If you've renamed the sheets, you may need to update the code. Note in the example, Sheet7 has been given the name "dfdf". If you wanted to update the filter there, you'd need to use;
    Sheets("dfdf").AutoFilter.ApplyFilter
    not;
    Sheets("Sheet7").AutoFilter.ApplyFilter

    It might be good to update the article including an example with a renamed sheet.


    Finally, if you want to monitor one sheet for data changes, but update filters on multiple sheets, then you only need one subroutine, placed in the code module of the worksheet you are monitoring. The code will look something like this;

    # (code must be placed in the worksheet to be monitored for data changes)
    Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("Sheet1").AutoFilter.ApplyFilter
    Sheets("Sheet2").AutoFilter.ApplyFilter
    Sheets("Sheet3").AutoFilter.ApplyFilter
    Sheets("Sheet4").AutoFilter.ApplyFilter
    End Sub
    • To post as a guest, your comment is unpublished.
      Luke H · 1 years ago
      Great explanation, thank you.

      But how do I trigger Sheets("Sheet3").AutoFilter.ApplyFilter when a new sheet is created?
      Since I cant write the code you mentioned on a sheet that doesnt exist yet
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Mike,
      Thanks for your detailed explanation.
  • To post as a guest, your comment is unpublished.
    Dave J · 2 years ago
    Works great and saves me a lot of time and messing about.. Really great tip.. Many thanks for your help
  • To post as a guest, your comment is unpublished.
    Asad · 2 years ago
    this command all fake do nothing . totally try but no use of.
  • To post as a guest, your comment is unpublished.
    SABRINA · 2 years ago
    I cannot get this to work for me at all. I am trying to take from a master sheet and have it only take the jobs that apply to certain project managers on each tab that is with their names. I also want it to auto refresh when I make changes.
  • To post as a guest, your comment is unpublished.
    Brian · 2 years ago
    I am doing this for a front in sheet were it the cell is set to =sheet1!E6. It will not apply filter when it changes. If i change the number in the back sheet it adjust front but does not filter. If adjust the formula to filter it criteria it does reapply. What can i do?
    • To post as a guest, your comment is unpublished.
      Asad · 2 years ago
      Use this
      Private Sub Work_Change(ByVal Target As Range)
      Activesheet.AutoFilter.ApplyFilter
      End Sub
  • To post as a guest, your comment is unpublished.
    Drew · 2 years ago
    I I want a change on one sheet to cause multiple other sheets to autofilter, how do I change this code? Ex: SheetA is changed, which causes Sheet1, Sheet2, and Sheet3 to apply its autofilter.

    Thanks!
  • To post as a guest, your comment is unpublished.
    Arif · 2 years ago
    Nice.. really i need it
  • To post as a guest, your comment is unpublished.
    VINICIUS · 2 years ago
    hello, how can i use all this in google finance?

    Tks