Viele Excel-Funktionalitäten stehen auch in VBA zur Verfügung - und die Automatischer Filter Methode ist eine solche Funktionalität.
Wenn Sie über ein Dataset verfügen und es anhand eines Kriteriums filtern möchten, können Sie dies ganz einfach über die Option Filter im Menüband Daten tun.
Und wenn Sie eine erweiterte Version davon wünschen, gibt es auch in Excel einen erweiterten Filter.
Warum dann überhaupt den AutoFilter in VBA verwenden?
Wenn Sie nur Daten filtern und einige grundlegende Dinge tun müssen, würde ich empfehlen, sich an die integrierte Filterfunktion zu halten, die die Excel-Schnittstelle bietet.
Sie sollten VBA Autofilter verwenden, wenn Sie die Daten als Teil Ihrer Automatisierung filtern möchten (oder wenn es Ihnen hilft, Zeit zu sparen, indem Sie die Daten schneller filtern).
Angenommen, Sie möchten die Daten schnell basierend auf einer Dropdown-Auswahl filtern und dann diese gefilterten Daten in ein neues Arbeitsblatt kopieren.
Dies kann zwar mit der integrierten Filterfunktion zusammen mit etwas Kopieren und Einfügen erfolgen, es kann jedoch viel Zeit in Anspruch nehmen, dies manuell zu tun.
In einem solchen Szenario kann die Verwendung von VBA Autofilter die Dinge beschleunigen und Zeit sparen.
Notiz: Dieses Beispiel (zum Filtern von Daten basierend auf einer Dropdown-Auswahl und zum Kopieren in ein neues Blatt) werde ich später in diesem Tutorial behandeln.
Excel VBA Autofilter-Syntax
Ausdruck. AutoFilter( _Feld_ , _Kriterien1_ , _Operator_ , _Kriterien2_ , _VisibleDropDown_ )
- Ausdruck: Dies ist der Bereich, auf den Sie den automatischen Filter anwenden möchten.
- Feld: [Optionales Argument] Dies ist die Spaltennummer, die Sie filtern möchten. Diese wird im Datensatz von links gezählt. Wenn Sie also Daten basierend auf der zweiten Spalte filtern möchten, wäre dieser Wert 2.
- Kriterien1: [Optionales Argument] Dies sind die Kriterien, nach denen Sie das Dataset filtern möchten.
- Operator: [Optionales Argument] Falls Sie auch Kriterium 2 verwenden, können Sie diese beiden Kriterien basierend auf dem Operator kombinieren. Folgende Operatoren stehen zur Verfügung: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
- Kriterien2: [Optionales Argument] Dies ist das zweite Kriterium, nach dem Sie den Datensatz filtern können.
- SichtbaresDropDown: [Optionales Argument] Sie können angeben, ob das Filter-Dropdown-Symbol in den gefilterten Spalten angezeigt werden soll oder nicht. Dieses Argument kann WAHR oder FALSCH sein.
Außer Ausdruck sind alle anderen Argumente optional.
Falls Sie kein Argument verwenden, werden die Filtersymbole einfach auf die Spalten angewendet oder entfernt.
Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub
Der obige Code würde einfach die Autofilter-Methode auf die Spalten anwenden (oder wenn sie bereits angewendet wurde, wird sie entfernt).
Dies bedeutet einfach, dass, wenn Sie die Filtersymbole in den Spaltenüberschriften nicht sehen können, Sie es sehen, wenn dieser obige Code ausgeführt wird, und wenn Sie es sehen können, wird es entfernt.
Falls Sie gefilterte Daten haben, werden die Filter entfernt und Ihnen der vollständige Datensatz angezeigt.
Sehen wir uns nun einige Beispiele für die Verwendung von Excel VBA Autofilter an, die die Verwendung deutlich machen.
Beispiel: Filtern von Daten basierend auf einer Textbedingung
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten ihn basierend auf der Spalte „Element“ filtern.
Der folgende Code würde alle Zeilen filtern, in denen das Element "Drucker" ist.
Sub FilterRows() Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer" End Sub
Der obige Code bezieht sich auf Sheet1 und darin auf A1 (das ist eine Zelle im Dataset).
Beachten Sie, dass wir hier Field:=2 verwendet haben, da die item-Spalte die zweite Spalte in unserem Dataset von links ist.
Wenn Sie jetzt denken, warum muss ich dies mit einem VBA-Code tun? Dies kann ganz einfach über die eingebaute Filterfunktion erfolgen.
Sie haben Recht!
Wenn dies alles ist, was Sie tun möchten, verwenden Sie besser die integrierte Filterfunktion.
Aber wenn Sie das verbleibende Tutorial lesen, werden Sie feststellen, dass dies mit etwas zusätzlichem Code kombiniert werden kann, um eine leistungsstarke Automatisierung zu erstellen.
Aber bevor ich Ihnen diese zeige, lassen Sie mich zunächst einige Beispiele behandeln, um Ihnen zu zeigen, was alle AutoFilter-Methoden können.
Hier klicken um die Beispieldatei herunterzuladen und mitzumachen.
Beispiel: Mehrere Kriterien (UND/ODER) in derselben Spalte
Angenommen, ich habe denselben Datensatz und möchte dieses Mal alle Datensätze filtern, bei denen das Element entweder „Drucker“ oder „Projektor“ ist.
Der folgende Code würde dies tun:
Sub FilterRowsOR() Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector" End Sub
Beachten Sie, dass ich hier die verwendet habe xlOR Operator.
Dies weist VBA an, beide Kriterien zu verwenden und die Daten zu filtern, wenn eines der beiden Kriterien erfüllt ist.
Ebenso können Sie auch die UND-Kriterien verwenden.
Wenn Sie beispielsweise alle Datensätze filtern möchten, deren Menge mehr als 10, aber weniger als 20 beträgt, können Sie den folgenden Code verwenden:
Sub FilterRowsAND() Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20" End Sub
Beispiel: Mehrere Kriterien mit unterschiedlichen Spalten
Angenommen, Sie haben den folgenden Datensatz.
Mit Autofilter können Sie mehrere Spalten gleichzeitig filtern.
Wenn Sie beispielsweise alle Datensätze filtern möchten, bei denen der Artikel "Drucker" und der Vertriebsmitarbeiter "Mark" ist, können Sie den folgenden Code verwenden:
Sub FilterRows() With Worksheets("Sheet1").Range("A1") .AutoFilter field:=2, Criteria1:="Printer" .AutoFilter field:=3, Criteria1:="Mark" End With End Sub
Beispiel: Top-10-Datensätze mit der AutoFilter-Methode filtern
Angenommen, Sie haben den folgenden Datensatz.
Unten ist der Code, der Ihnen die Top 10 Datensätze anzeigt (basierend auf der Mengenspalte):
Sub FilterRowsTop10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Items End Sub
Im obigen Code habe ich ActiveSheet verwendet. Sie können den Blattnamen verwenden, wenn Sie möchten.
Beachten Sie, dass Sie in diesem Beispiel, wenn Sie die Top-5-Elemente erhalten möchten, einfach die Zahl in ändern Kriterien1:=”10″ von 10 bis 5.
Für die Top-5-Artikel lautet der Code also:
Sub FilterRowsTop5() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operator:=xlTop10Items End Sub
Es mag seltsam aussehen, aber egal wie viele Top-Elemente Sie möchten, der Operator-Wert bleibt immer erhalten xlTop10Items.
In ähnlicher Weise würde der folgende Code Ihnen die unteren 10 Elemente anzeigen:
Sub FilterRowsBottom10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10Items End Sub
Und wenn Sie die unteren 5 Elemente haben möchten, ändern Sie die Zahl in Kriterien1:=”10″ von 10 bis 5.
Beispiel: Top 10 Prozent mit der AutoFilter-Methode filtern
Angenommen, Sie haben denselben Datensatz (wie in den vorherigen Beispielen verwendet).
Unten ist der Code, der Ihnen die Top-10-Prozent-Datensätze anzeigt (basierend auf der Mengenspalte):
Sub FilterRowsTop10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Percent End Sub
Da wir in unserem Datensatz 20 Datensätze haben, werden die Top 2 Datensätze zurückgegeben (das sind 10 % der Gesamtdatensätze).
Beispiel: Verwenden von Platzhalterzeichen in Autofilter
Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Wenn Sie alle Zeilen filtern möchten, in denen der Artikelname das Wort „Board“ enthält, können Sie den folgenden Code verwenden:
Sub FilterRowsWildcard() Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="*Board*" End Sub
Im obigen Code habe ich das Platzhalterzeichen * (Sternchen) vor und nach dem Wort „Board“ (das sind die Kriterien) verwendet.
Ein Stern kann eine beliebige Anzahl von Zeichen darstellen. Dies würde also jedes Element filtern, das das Wort "Board" enthält.
Beispiel: Gefilterte Zeilen in ein neues Blatt kopieren
Wenn Sie die Datensätze nicht nur nach Kriterien filtern, sondern auch die gefilterten Zeilen kopieren möchten, können Sie das untenstehende Makro verwenden.
Es kopiert die gefilterten Zeilen, fügt ein neues Arbeitsblatt hinzu und fügt diese kopierten Zeilen dann in das neue Blatt ein.
Sub CopyFilteredRows() Dim rng As Range Dim ws As Worksheet If Worksheets("Sheet1").AutoFilterMode = False Then MsgBox "Es gibt keine gefilterten Zeilen" Exit Sub End If Set rng = Worksheets("Sheet1").AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range("A1") End Sub
Der obige Code würde überprüfen, ob in Sheet1 gefilterte Zeilen vorhanden sind oder nicht.
Wenn keine gefilterten Zeilen vorhanden sind, wird ein Meldungsfeld mit diesem Hinweis angezeigt.
Und wenn gefilterte Zeilen vorhanden sind, werden diese kopiert, ein neues Arbeitsblatt eingefügt und diese Zeilen in das neu eingefügte Arbeitsblatt eingefügt.
Beispiel: Daten basierend auf einem Zellenwert filtern
Mit Autofilter in VBA zusammen mit einer Dropdown-Liste können Sie eine Funktion erstellen, bei der alle Datensätze für dieses Element gefiltert werden, sobald Sie ein Element aus der Dropdown-Liste auswählen.
Etwas wie unten gezeigt:
Hier klicken um die Beispieldatei herunterzuladen und mitzumachen.
Diese Art von Konstrukt kann nützlich sein, wenn Sie Daten schnell filtern und dann in Ihrer Arbeit weiter verwenden möchten.
Unten ist der Code, der dies tut:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then If Range("B2") = "All" Then Range("A5").AutoFilter Else Range("A5").AutoFilter Field :=2, Criteria1:=Range("B2") End If End If End Sub
Dies ist ein Arbeitsblatt-Ereigniscode, der nur ausgeführt wird, wenn eine Änderung im Arbeitsblatt vorliegt und die Zielzelle B2 ist (wo wir die Dropdown-Liste haben).
Außerdem wird eine If Then Else-Bedingung verwendet, um zu überprüfen, ob der Benutzer „Alle“ aus der Dropdown-Liste ausgewählt hat. Wenn Alle ausgewählt ist, wird der gesamte Datensatz angezeigt.
Dieser Code wird NICHT in einem Modul platziert.
Stattdessen muss es im Backend des Arbeitsblatts platziert werden, das diese Daten enthält.
Hier sind die Schritte, um diesen Code in das Arbeitsblatt-Codefenster einzufügen:
- Öffnen Sie den VB-Editor (Tastaturkürzel - ALT + F11).
- Doppelklicken Sie im Projekt-Explorer-Bereich auf den Arbeitsblattnamen, in dem Sie diese Filterfunktionalität verwenden möchten.
- Kopieren Sie im Arbeitsblattcodefenster den obigen Code und fügen Sie ihn ein.
- Schließen Sie den VB-Editor.
Wenn Sie jetzt die Dropdown-Liste verwenden, werden die Daten automatisch gefiltert.
Dies ist ein Arbeitsblatt-Ereigniscode, der nur ausgeführt wird, wenn eine Änderung im Arbeitsblatt vorliegt und die Zielzelle B2 ist (wo wir die Dropdown-Liste haben).
Außerdem wird eine If Then Else-Bedingung verwendet, um zu überprüfen, ob der Benutzer „Alle“ aus der Dropdown-Liste ausgewählt hat. Wenn Alle ausgewählt ist, wird der gesamte Datensatz angezeigt.
Schalten Sie Excel AutoFilter mit VBA ein/aus
Wenn Sie Autofilter auf einen Zellbereich anwenden, sind möglicherweise bereits einige Filter vorhanden.
Sie können den folgenden Code verwenden, um alle vorab angewendeten automatischen Filter zu deaktivieren:
Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = False End Sub
Dieser Code überprüft die gesamten Blätter und entfernt alle angewendeten Filter.
Wenn Sie Filter nicht für das gesamte Blatt, sondern nur für ein bestimmtes Dataset deaktivieren möchten, verwenden Sie den folgenden Code:
Sub TurnOFFAutoFilter() If Worksheets("Sheet1").Range("A1").AutoFilter Then Worksheets("Sheet1").Range("A1").AutoFilter End If End Sub
Der obige Code prüft, ob bereits Filter vorhanden sind oder nicht.
Wenn bereits Filter angewendet wurden, werden sie entfernt, andernfalls wird nichts unternommen.
Wenn Sie AutoFilter aktivieren möchten, verwenden Sie den folgenden Code:
Sub TurnOnAutoFilter() If Not Worksheets("Sheet1").Range("A4").AutoFilter Then Worksheets("Sheet1").Range("A4").AutoFilter End If End Sub
Überprüfen Sie, ob AutoFilter bereits angewendet wurde
Wenn Sie ein Blatt mit mehreren Datensätzen haben und sicherstellen möchten, dass noch keine Filter vorhanden sind, können Sie den folgenden Code verwenden.
Sub CheckforFilters() If ActiveSheet.AutoFilterMode = True Then MsgBox "Es sind bereits Filter vorhanden" Else MsgBox "Es sind keine Filter vorhanden" End If End Sub
Dieser Code verwendet eine Meldungsfeldfunktion, die die Meldung "Es sind bereits Filter vorhanden" anzeigt, wenn Filter auf dem Blatt gefunden werden, andernfalls wird "Es sind keine Filter vorhanden" angezeigt.
Alle Daten anzeigen
Wenn Sie Filter auf das Dataset angewendet haben und alle Daten anzeigen möchten, verwenden Sie den folgenden Code:
Sub ShowAllData() Wenn ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End Sub
Der obige Code überprüft, ob der FilterMode TRUE oder FALSE ist.
Wenn es wahr ist, bedeutet dies, dass ein Filter angewendet wurde und die ShowAllData-Methode verwendet, um alle Daten anzuzeigen.
Beachten Sie, dass die Filter dadurch nicht entfernt werden. Die Filtersymbole können weiterhin verwendet werden.
Verwenden von AutoFilter auf geschützten Blättern
Wenn Sie ein Blatt schützen, funktionieren die Filter standardmäßig nicht.
Falls Sie bereits Filter haben, können Sie AutoFilter aktivieren, um sicherzustellen, dass es auch auf geschützten Blättern funktioniert.
Aktivieren Sie dazu die Option Autofilter verwenden, während Sie das Blatt schützen.
Dies funktioniert zwar, wenn Sie bereits Filter installiert haben, aber falls Sie versuchen, Autofilter mit einem VBA-Code hinzuzufügen, funktioniert es nicht.
Da das Blatt geschützt ist, kann kein Makro ausgeführt und keine Änderungen am Autofilter vorgenommen werden.
Sie müssen also einen Code verwenden, um das Arbeitsblatt zu schützen und sicherzustellen, dass darin automatische Filter aktiviert sind.
Dies kann nützlich sein, wenn Sie einen dynamischen Filter erstellt haben (etwas, das ich im Beispiel behandelt habe - "Daten basierend auf einem Zellenwert filtern").
Unten finden Sie den Code, der das Blatt schützt, Ihnen aber gleichzeitig die Verwendung von Filtern und VBA-Makros ermöglicht.
Private Sub Workbook_Open() With Worksheets("Sheet1") .EnableAutoFilter = True .Protect Password:="password", Contents:=True, UserInterfaceOnly:=True End With End Sub
Dieser Code muss im Codefenster von ThisWorkbook platziert werden.
Hier sind die Schritte zum Einfügen des Codes in das Codefenster von ThisWorkbook:
- Öffnen Sie den VB-Editor (Tastaturkürzel - ALT + F11).
- Doppelklicken Sie im Bereich Projekt-Explorer auf das Objekt ThisWorkbook.
- Kopieren Sie im sich öffnenden Codefenster den obigen Code und fügen Sie ihn ein.
Sobald Sie die Arbeitsmappe öffnen und Makros aktivieren, wird das Makro automatisch ausgeführt und Sheet1 geschützt.
Zuvor wird jedoch „EnableAutoFilter = True“ angegeben, was bedeutet, dass die Filter auch im geschützten Blatt funktionieren würden.
Außerdem wird das Argument „UserInterfaceOnly“ auf „True“ gesetzt. Dies bedeutet, dass der VBA-Makrocode weiterhin funktioniert, während das Arbeitsblatt geschützt ist.
Die folgenden VBA-Tutorials könnten Ihnen auch gefallen:
- Excel VBA-Schleifen.
- Zellen mit fetter Schriftformatierung filtern.
- Aufzeichnen eines Makros.
- Daten mit VBA sortieren.
- Sortieren Sie Arbeitsblatt-Registerkarten in Excel.
