Excel VBA Autofilter: Ein komplettes Handbuch mit Beispielen

Viele Excel-Funktionen können auch in VBA verwendet werden – und die Autofilter-Methode ist eine dieser Funktionen.

Wenn Sie einen Datensatz haben und ihn nach einem Kriterium filtern möchten, können Sie dies ganz einfach mit der Option „Filter“ im Menüband „Daten“ tun.

Und wenn Sie eine fortgeschrittenere Version davon wünschen, gibt es auch einen erweiterten Filter in Excel.

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 eingebaute Filterfunktionalität zu halten, die die Excel-Oberfläche bietet.

Sie sollten den VBA-Autofilter verwenden, wenn Sie die Daten als Teil Ihrer Automatisierung filtern wollen (oder wenn er Ihnen hilft, Zeit zu sparen, indem er das Filtern der Daten beschleunigt).

Angenommen, Sie möchten die Daten schnell auf der Grundlage einer Dropdown-Auswahl filtern und diese gefilterten Daten dann in ein neues Arbeitsblatt kopieren.

Dies kann zwar mit der integrierten Filterfunktionalität zusammen mit etwas Kopieren/Einfügen erfolgen, aber es kann viel Zeit kosten, dies manuell zu tun.

In einem solchen Szenario kann die Verwendung von VBA Autofilter die Dinge beschleunigen und Zeit sparen.

Hinweis: Ich werde dieses Beispiel (zum Filtern von Daten auf der Grundlage einer Dropdown-Auswahl und Kopieren in ein neues Blatt) später in diesem Tutorial behandeln.

Excel VBA Autofilter Syntax

Expression. AutoFilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )

• Ausdruck: Dies ist der Bereich, auf den Sie den Autofilter anwenden möchten.
• Feld: Dies ist die Nummer der Spalte, die gefiltert werden soll. Diese wird im Datensatz von links gezählt. Wenn Sie also Daten auf der Grundlage der zweiten Spalte filtern möchten, wäre dieser Wert 2.
• Kriterium1: Dies ist das Kriterium, nach dem Sie den Datensatz filtern möchten.
• Operator: Wenn Sie auch Kriterium 2 verwenden, können Sie diese beiden Kriterien mit Hilfe des Operators kombinieren. Folgende Operatoren stehen zur Verfügung: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Criteria2: Dies ist das zweite Kriterium, nach dem Sie den Datensatz filtern können.
• VisibleDropDown: Sie können angeben, ob das Dropdown-Symbol des Filters in den gefilterten Spalten erscheinen soll oder nicht. Dieses Argument kann TRUE oder FALSE sein.

Abgesehen von Expression sind alle anderen Argumente optional.

Wenn Sie kein Argument verwenden, werden die Filtersymbole einfach auf die Spalten angewendet oder entfernt.

Sub FilterRows()Worksheets("Filter Data").Range("A1").AutoFilterEnd Sub

Der obige Code würde einfach die Autofilter-Methode auf die Spalten anwenden (oder, wenn sie bereits angewendet wurde, entfernen).

Das bedeutet einfach, dass, wenn Sie die Filtersymbole in den Spaltenüberschriften nicht sehen können, Sie sie sehen werden, wenn der obige Code ausgeführt wird, und wenn Sie sie sehen können, werden sie entfernt.

Falls Sie gefilterte Daten haben, werden die Filter entfernt und der vollständige Datensatz angezeigt.

Nun lassen Sie uns einige Beispiele für die Verwendung von Excel VBA Autofilter sehen, die seine Verwendung deutlich machen werden.

Beispiel: Filtern von Daten anhand einer Textbedingung

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten ihn anhand 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 Blatt1 und darin auf A1 (eine Zelle im Datensatz).

Beachten Sie, dass wir hier Feld:=2 verwendet haben, da die Elementspalte die zweite Spalte in unserem Datensatz von links ist.

Aber bevor ich Ihnen diese zeige, möchte ich Ihnen anhand einiger Beispiele zeigen, was die AutoFilter-Methode alles kann.

Klicken Sie hier, um die Beispieldatei herunterzuladen und mitzumachen.

Beispiel: Mehrere Kriterien (AND/OR) in derselben Spalte

Angenommen, ich habe denselben Datensatz und möchte diesmal 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 den xlOR-Operator verwendet habe.

Damit wird VBA angewiesen, beide Kriterien zu verwenden und die Daten zu filtern, wenn eines der beiden Kriterien erfüllt ist.

Ahnlich können Sie auch die UND-Kriterien verwenden.

Wenn Sie beispielsweise alle Datensätze filtern möchten, bei denen die 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 verschiedenen 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 Verkäufer „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 WithEnd Sub

Beispiel: Filtern der 10 wichtigsten Datensätze mit der AutoFilter-Methode

Angenommen, Sie haben den folgenden Datensatz:

Nachfolgend finden Sie den Code, der Ihnen die 10 wichtigsten Datensätze (basierend auf der Mengenspalte) anzeigt:

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10ItemsEnd 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 ersten 5 Elemente erhalten möchten, einfach die Zahl in Criteria1:=“10″ von 10 auf 5 ändern.

Für die ersten 5 Elemente würde der Code also lauten:

Sub FilterRowsTop5()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operator:=xlTop10ItemsEnd Sub

Es sieht vielleicht komisch aus, aber egal wie viele Elemente Sie haben möchten, der Operator-Wert bleibt immer xlTop10Items.

Mit dem folgenden Code erhalten Sie die untersten 10 Elemente:

Sub FilterRowsBottom10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10ItemsEnd Sub

Und wenn Sie die untersten 5 Elemente wünschen, ändern Sie die Zahl in Criteria1:=“10″ von 10 auf 5.

Beispiel: Filtern der obersten 10 Prozent mit der AutoFilter-Methode

Angenommen, Sie haben denselben Datensatz (wie in den vorherigen Beispielen).

Nachfolgend finden Sie den Code, der Ihnen die obersten 10 Prozent der Datensätze (basierend auf der Spalte „Menge“) liefert:

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10PercentEnd Sub

In unserem Datensatz, da wir 20 Datensätze haben, werden die obersten 2 Datensätze zurückgegeben (das sind 10 % der gesamten Datensätze).

Beispiel: Verwendung von Platzhalterzeichen im Autofilter

Angenommen, Sie haben einen Datensatz wie unten gezeigt:

Wenn Sie alle Zeilen filtern möchten, in denen der Elementname 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 das Kriterium ist) verwendet.

Ein Sternchen kann für eine beliebige Anzahl von Zeichen stehen. 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 folgende Makro verwenden.

Es kopiert die gefilterten Zeilen, fügt ein neues Arbeitsblatt hinzu und fügt die kopierten Zeilen dann in das neue Blatt ein.

Sub CopyFilteredRows()Dim rng As RangeDim ws As WorksheetIf Worksheets("Sheet1").AutoFilterMode = False ThenMsgBox "There are no filtered rows"Exit SubEnd IfSet rng = Worksheets("Sheet1").AutoFilter.RangeSet ws = Worksheets.Addrng.Copy Range("A1")End Sub

Der obige Code prüft, ob in Blatt1 gefilterte Zeilen vorhanden sind oder nicht.

Wenn keine gefilterten Zeilen vorhanden sind, wird eine entsprechende Meldung 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 auf der Grundlage eines Zellwerts filtern

Mit Autofilter in VBA und einer Dropdown-Liste können Sie eine Funktion erstellen, bei der, sobald Sie ein Element aus der Dropdown-Liste auswählen, alle Datensätze für dieses Element gefiltert werden.

Etwas wie unten gezeigt:

Klicken Sie hier, um die Beispieldatei herunterzuladen und zu folgen.

Diese Art von Konstrukt kann nützlich sein, wenn Sie Daten schnell filtern und dann in Ihrer Arbeit weiter verwenden möchten.

Nachfolgend finden Sie den Code, der dies ermöglicht:

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = "$B" Then If Range("B2") = "All" Then Range("A5").AutoFilter Else Range("A5").AutoFilter Field:=2, Criteria1:=Range("B2") End IfEnd IfEnd Sub

Dies ist ein Arbeitsblatt-Ereigniscode, der nur ausgeführt wird, wenn es eine Änderung im Arbeitsblatt gibt und die Zielzelle B2 ist (in der sich das Dropdown befindet).

Außerdem wird eine If Then Else-Bedingung verwendet, um zu prüfen, ob der Benutzer „Alle“ aus dem Dropdown ausgewählt hat. Wenn „Alle“ ausgewählt wurde, wird der gesamte Datensatz angezeigt.

Dieser Code wird NICHT in einem Modul platziert.

Anstattdessen muss er im Backend des Arbeitsblatts platziert werden, das diese Daten enthält.

Hier sind die Schritte, um diesen Code in das Codefenster des Arbeitsblatts einzufügen:

1. Öffnen Sie den VB-Editor (Tastaturkürzel – ALT + F11).
2. Doppelklicken Sie im Projekt-Explorer-Fenster auf den Namen des Arbeitsblatts, in dem Sie diese Filterfunktion wünschen.
3. Kopieren Sie im Arbeitsblattcodefenster den obigen Code und fügen Sie ihn ein.
4. Schließen Sie den VB-Editor.

Wenn Sie nun die Dropdown-Liste verwenden, werden die Daten automatisch gefiltert.

Dies ist ein Arbeitsblatt-Ereigniscode, der nur ausgeführt wird, wenn es eine Änderung im Arbeitsblatt gibt und die Zielzelle B2 ist (wo wir die Auswahlliste haben).

Außerdem wird eine If Then Else-Bedingung verwendet, um zu prüfen, ob der Benutzer „Alle“ aus der Auswahlliste ausgewählt hat. Wenn „Alle“ ausgewählt ist, wird der gesamte Datensatz angezeigt.

Excel-Autofilter mit VBA ein- und ausschalten

Wenn Sie Autofilter auf einen Zellbereich anwenden, sind möglicherweise bereits einige Filter vorhanden.

Sie können den folgenden Code verwenden, um alle bereits angewendeten Autofilter zu deaktivieren:

Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = FalseEnd Sub

Dieser Code überprüft die gesamten Blätter und entfernt alle Filter, die angewendet wurden.

Wenn Sie die Filter nicht für das gesamte Blatt, sondern nur für einen bestimmten Datensatz deaktivieren möchten, verwenden Sie den folgenden Code:

Sub TurnOFFAutoFilter() If Worksheets("Sheet1").Range("A1").AutoFilter Then Worksheets("Sheet1").Range("A1").AutoFilter End IfEnd Sub

Der obige Code prüft, ob bereits Filter angewendet wurden oder nicht.

Wenn bereits Filter angewendet wurden, werden sie entfernt, andernfalls geschieht nichts.

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 IfEnd Sub

Prüfen, ob AutoFilter bereits angewendet wurde

Wenn Sie ein Blatt mit mehreren Datensätzen haben und sicherstellen möchten, dass keine Filter bereits angewendet wurden, können Sie den folgenden Code verwenden.

Sub CheckforFilters()If ActiveSheet.AutoFilterMode = True ThenMsgBox "There are Filters already in place"ElseMsgBox "There are no filters"End IfEnd Sub

Dieser Code verwendet eine Nachrichtenboxfunktion, die die Meldung „Es sind bereits Filter vorhanden“ anzeigt, wenn sie Filter auf dem Blatt findet, andernfalls zeigt sie „Es sind keine Filter vorhanden“ an.

Alle Daten anzeigen

Wenn Sie Filter auf den Datensatz angewendet haben und alle Daten anzeigen möchten, verwenden Sie den folgenden Code:

Sub ShowAllData()If ActiveSheet.FilterMode Then ActiveSheet.ShowAllDataEnd Sub

Der obige Code überprüft, ob der FilterMode TRUE oder FALSE ist.

Wenn er wahr ist, bedeutet dies, dass ein Filter angewendet wurde, und er verwendet die Methode ShowAllData, um alle Daten anzuzeigen.

Beachten Sie, dass dies die Filter nicht entfernt. Die Filtersymbole können weiterhin verwendet werden.

Verwenden von AutoFilter auf geschützten Blättern

Standardmäßig funktionieren die Filter nicht, wenn Sie ein Blatt schützen.

Sollten Sie bereits Filter eingesetzt haben, können Sie AutoFilter aktivieren, um sicherzustellen, dass es auch auf geschützten Blättern funktioniert.

Zu diesem Zweck aktivieren Sie die Option Autofilter verwenden, während Sie das Blatt schützen.

Dies funktioniert zwar, wenn Sie bereits Filter installiert haben, aber wenn Sie versuchen, Autofilter mithilfe eines VBA-Codes hinzuzufügen, wird dies nicht funktionieren.

Da das Blatt geschützt ist, kann kein Makro ausgeführt werden, das Änderungen am Autofilter vornimmt.

So müssen Sie einen Code verwenden, um das Arbeitsblatt zu schützen und sicherzustellen, dass Autofilter darin aktiviert sind.

Dies kann nützlich sein, wenn Sie einen dynamischen Filter erstellt haben (etwas, das ich im Beispiel – ‚Daten auf der Grundlage eines Zellwerts filtern‘ – behandelt habe).

Nachfolgend finden Sie den Code, der das Blatt schützt, aber gleichzeitig die Verwendung von Filtern und VBA-Makros darin ermöglicht.

Private Sub Workbook_Open()With Worksheets("Sheet1").EnableAutoFilter = True.Protect Password:="password", Contents:=True, UserInterfaceOnly:=TrueEnd WithEnd Sub

Dieser Code muss im Codefenster von ThisWorkbook platziert werden.

Hier sind die Schritte, um den Code im Codefenster von ThisWorkbook zu platzieren:

1. Öffnen Sie den VB-Editor (Tastaturkürzel – ALT + F11).
2. Doppelklicken Sie im Projekt-Explorer-Fenster auf das Objekt ThisWorkbook.
3. In dem sich öffnenden Codefenster kopieren Sie den obigen Code und fügen ihn ein.

Sobald Sie die Arbeitsmappe öffnen und Makros aktivieren, wird das Makro automatisch ausgeführt und Blatt1 geschützt.

Zuvor wird jedoch ‚EnableAutoFilter = True‘ festgelegt, was bedeutet, dass die Filter auch im geschützten Blatt funktionieren würden.

Außerdem wird das Argument ‚UserInterfaceOnly‘ auf ‚True‘ gesetzt. Das bedeutet, dass der Code der VBA-Makros weiterhin funktioniert, während das Arbeitsblatt geschützt ist.

Die folgenden VBA-Tutorials könnten Ihnen auch gefallen:

• Excel VBA-Schleifen.
• Zellen mit Fettschriftformatierung filtern.
• Ein Makro aufzeichnen.
• Daten mit VBA sortieren.
• Arbeitsblattregisterkarten in Excel sortieren.