Wie kann man einen Excel-Autofilter automatisch aktualisieren, wenn Daten geändert werden?

Das Austauschen des Codes mit diesem scheint den Trick ebenfalls zu tun (zumindest in Excel 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Ich stellte fest, dass dies nicht funktionierte, wenn ich mit Tabellen arbeitete. Der Filter war nicht auf dem Blatt, sondern auf der Tabelle. Dieser Code hat den Trick gemacht

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")
         .AutoFilter.ApplyFilter
    End With
End Sub

Ich habe die Informationen hier gefunden: http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp

Ich verwende auch ein VBA/Makro, das auf dem Worksheet_Change-Ereignis basiert, aber mein Ansatz ist etwas anders… Ok, zuerst der Code und dann die Erklärungen:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' first remove filter
    ActiveSheet.Range("$L$1:$L$126").AutoFilter Field:=1        
    ' then apply it again
    ActiveSheet.Range("$L$1:$L$126").AutoFilter Field:=1, Criteria1:="<>0"
End Sub

(Verwenden Sie die Tastenkombination Alt+F11, um das Entwicklungspanel erscheinen zu lassen und fügen Sie den Code in das Arbeitsblatt ein, das den Filter enthält, der automatisch aktualisiert werden soll).

In meinem Beispiel gehe ich davon aus, dass ich einen einfachen Filter auf eine einzelne Spalte (in meinem Fall L) habe und dass sich mein Datenbereich auf Zeilen von 1 (auch wenn er eine Überschrift enthalten kann) bis 126 (wählen Sie eine Zahl, die groß genug ist, um sicher zu sein) erstreckt. Die Bedienung ist einfach: wenn sich etwas auf meinem Blatt ändert, wird der Filter auf dem angegebenen Bereich entfernt/neu angewendet, damit er aktualisiert wird. Was hier ein wenig der Erklärung bedarf, sind Field und Criteria.

Das Feld ist ein ganzzahliger Offset des Bereichs. In meinem Fall habe ich nur einen einspaltigen Filter und der Bereich wird durch eine einzelne Spalte (L) gebildet, die die erste im Bereich ist (daher verwende ich 1 als Wert).

Das Kriterium ist eine Zeichenfolge, die den Filter beschreibt, der auf den Datenbereich angewendet werden soll. In meinem Beispiel möchte ich nur Zeilen anzeigen, in denen sich die Spalte L von 0 unterscheidet (daher habe ich “<>0” verwendet).

Das ist alles. Weitere Hinweise zur Methode Range.AutoFilter finden Sie unter: https://msdn.microsoft.com/en-us/library/office/ff193884.aspx

Klicken Sie mit der rechten Maustaste auf Ihren Blattnamen, wählen Sie “Code anzeigen” und fügen Sie den unten stehenden Code ein. Klicken Sie nach dem Einfügen auf das Excel-Symbol unter “Datei” oben links oder geben Sie Alt-F11 ein, um zur Tabellenkalkulationsansicht zurückzukehren.

Damit wird die automatische Aktualisierung aktiviert. Vergessen Sie nicht, die Datei in einem Format mit Makrounterstützung lie .xlsm zu speichern.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Me.FilterMode = True Then
        With Application
           .EnableEvents = False
           .ScreenUpdating = False
        End With

        With ActiveWorkbook
            .CustomViews.Add ViewName:="Mine", RowColSettings:=True
          Me.AutoFilterMode = False
            .CustomViews("Mine").Show
            .CustomViews("Mine").Delete
        End With

         With Application
           .EnableEvents = True
           .ScreenUpdating = True
        End With
    End If

End Sub

Nur um die Antwort(en) zu konsolidieren:

Sorin sagt:

Klicken Sie mit der rechten Maustaste auf Ihren Blattnamen, wählen Sie “Code anzeigen” und fügen Sie den Code unten ein. Klicken Sie nach dem Einfügen auf das Excel-Symbol unter “Datei” oben links oder geben Sie Alt-F11 ein, um zur Tabellenansicht zurückzukehren.

Damit wird die automatische Aktualisierung aktiviert. Vergessen Sie nicht, die Datei in einem Format mit Makrounterstützung lie .xlsm zu speichern.

Und Chris hat diesen Code verwendet (den ich erst 2010 gemacht habe):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Wenn Sie den Beitrag nicht erweitern, sehen Sie nur die lange Antwort! ;)

using "data, from table"/power query in excel, which gives us option to refresh data when opening file. (also auto sort, and index column (number filtered rows automatically )) This will create result in another sheet. -select data required using mouse (rows and columns) -click on data tab, from table -in the last column, exclude blanks (optional, if you want to to display only filled cells) -add column, index column (optional, if you want to add row number to filtered results) -close and load to to edit again, click on query tab, and then on edit click on design tab in excel, on the arrow below refresh, connection properties, refresh data when opening file. adapted from: https://www.excelcampus.com/tips/sort-drop-down-lists-automatically/ part: 3. Sorting Drop Down Lists Using Power Query you can also copy data from sheet1 if not empty, for example field a1. copy this to a1 field in sheet2: =IF(Sheet1!A1"";Sheet1!A1;"")

Sorry, zu wenig Vertreter für einen Kommentar. (Admins, fühlen Sie sich frei, dies in einen Kommentar oben zu schneiden.) Benutzer “danicotra” Antwort beginnend mit “Ich verwende ein VBA/Macro basierend auf Worksheet_Change-Ereignis auch, aber mein Ansatz…” mit ‘ zuerst Filter entfernen ’ dann wieder anwenden ist die richtige Lösung, wenn Sie Excel 2007+ verwenden. Allerdings ist .AutoFilter.ApplyFilter in XL03 und früher ungültig, daher zeige ich den Weg unten.

Ich bitte darum, dass wahre Experten und Gurus den Code lesen, denn ich bin mir ziemlich sicher, dass es sich um erstklassiges Material handelt. Vielleicht kann die unerklärliche Anzahl an Downvotes für diese Antwort rückgängig gemacht werden, wenn die Leute sehen, was für gute Sachen unten gemacht werden.

danicotra verwendete ein vereinfachtes Beispiel. Eigentlich kann man das auch allgemeiner machen. Nehmen Sie mit ActiveSheet für das folgende (oder ein anderes Blattobjekt) an:

1. Speichern Sie den Bereich des Autofilters. Er hat .AutoFilter.Filters.Count Spalten und (.AutoFilter.Range.Count/.AutoFilter.Filters.Count) Zeilen, gespeichert in rngAutofilter

2. Sammeln Sie in einem Array myAutofilters alle 4 Eigenschaften der einzelnen .AutoFilter.Filters.Count Autofilter-Elemente, wobei Sie darauf achten müssen, dass Sie “Anwendungsdefinierte Fehler” vermeiden, wenn .On oder .Operator falsch ist. (myAutofilters würde auf die Anzahl der Zeilen und Spalten in Schritt 1 reDim’d werden)

3. Schalten Sie den Filter aus, aber behalten Sie die Dropdowns mit .ShowAllData

4. Setzen Sie für jedes Filterelement, das gemäß Ihrem gespeicherten Array .On war, 3 der 4 Eigenschaften von jedem der .AutoFilter.Filters.Count zurück. Achten Sie wieder darauf, dass Sie “Anwendungsdefinierte Fehler” vermeiden, wenn .Operator falsch ist, also für jedes Element “i”, rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i,2) oder rngAutofilter. AutoFilter Field:=i, Criteria1:=myAutofilters(i,2), Operator:=myAutofilters(i,3), Criteria2:=myAutofilters(i,4)

Jetzt wird der Autofilter wiederhergestellt, und zwar über denselben Bereich wie vor Beginn Ihres Codes, aber mit aktualisiertem Autofilter für Datenänderungen.

Public myAutofilters As Variant, rngAutofilter As Range 'Public
Sub SaveAndRestoreAutofilters()
  'This will update the autofilter display to recognize data changes by turning autofilter off and then on, preserving all characteristics
  'Note, XL2007 and later have .autofilter.applyfilter, but not the invaluable XL03 and earlier
  Dim i As Long, iNumAutofilters As Long, iNumActiveAutofilters As Long
  iNumActiveAutofilters = SaveAutoFilterInfo(iNumAutofilters) 'NOTE! Use CALL or assignment to prevent parentheses from forcing ByVal !
  If iNumActiveAutofilters < 1 Then
      Application.StatusBar = "0 ACTIVE filters;" & iNumAutofilters & " autofilters"
      Exit Sub
  End If
  ActiveSheet.ShowAllData

  Rem Here optionally do stuff which can include changing data or toggling autofilter columns

  For i = 1 To iNumAutofilters
      If myAutofilters(i, 1) Then
          If myAutofilters(i, 3) <> 0 Then 'then .Operator is something, so set it and Criteria2, else just Criteria1
              rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i, 2), Operator:=myAutofilters(i, 3), Criteria2:=myAutofilters(i, 4) ', On:=true by rule
          Else
              rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i, 2) ', On:=true by rule (it's R/O anyway)
          End If
          Rem Selection.AutoFilter Field:=i 'How you'd "turn off" only a single column's autofiltering. FYI .On is R/O!
      End If
      'activesheet.autofiltermode=false 'just FYI, how you comprehensively turn off filtering on a sheet (erasing the dropdowns and criteria and filter range!)
  Next i
End Sub
Function SaveAutoFilterInfo(iNumAutofilters As Long) As Long
  Dim i As Long, iRowsAutofiltered As Long
  SaveAutoFilterInfo = 0 'counts the number that are .On, and returns the total
  iNumAutofilters = ActiveSheet.AutoFilter.Range.Columns.Count
  If ActiveSheet.AutoFilter.Filters.Count <> iNumAutofilters Then MsgBox "I can't explain this. All bets are off. Aborting.": Exit function
  ReDim myAutofilters(1 To iNumAutofilters, 4)
  For i = 1 To iNumAutofilters
      myAutofilters(i, 1) = ActiveSheet.AutoFilter.Filters(i).On
      If myAutofilters(i, 1) Then
          SaveAutoFilterInfo = SaveAutoFilterInfo + 1
          myAutofilters(i, 2) = ActiveSheet.AutoFilter.Filters(i).Criteria1
          myAutofilters(i, 3) = ActiveSheet.AutoFilter.Filters(i).Operator
          If myAutofilters(i, 3) <> 0 Then 'then is either xlAnd, xlOr, etc., and there's a second criteria
              myAutofilters(i, 4) = ActiveSheet.AutoFilter.Filters(i).Criteria2
          End If
      End If
  Next i
  iRowsAutofiltered = ActiveSheet.AutoFilter.Range.Count / ActiveSheet.AutoFilter.Range.Columns.Count
  Set rngAutofilter = Cells(ActiveSheet.AutoFilter.Range.Row, ActiveSheet.AutoFilter.Range.Column).Resize(iRowsAutofiltered, iNumAutofilters)
End Function