Der Autofilter ist eine der meistgenutzten Funktionen in Excel. Per VBA lässt er sich vollständig steuern: Filter setzen, Ergebnisse weiterverarbeiten und wieder aufheben. Dieser Beitrag fasst die häufigsten Aufgaben mit erprobtem Code zusammen.
Alle Beispiele gehen von einer Liste auf dem Blatt „Daten“ aus, die in A1 beginnt: Spalte A Artikel, Spalte B Kategorie, Spalte C Lieferdatum. CurrentRegion erfasst die zusammenhängende Liste automatisch, auch wenn sie wächst. Field ist die Spaltennummer innerhalb der Liste.
Die Kurzlösung: nach mehreren Werten filtern
Ein Filter auf die Kategorien a, b und g, so als hätten Sie im Filterdialog genau diese Werte angehakt:
Sub FilterMehrereWerte()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Daten")
ws.Range("A1").CurrentRegion.AutoFilter Field:=2, _
Criteria1:=Array("a", "b", "g"), Operator:=xlFilterValues
End Sub
Entscheidend ist Operator:=xlFilterValues. Damit übernimmt der Filter beliebig viele Werte aus dem Array. Die Werte können auch aus Zellen kommen, etwa Criteria1:=Application.Transpose(ws.Range("H2:H4").Value).
Nach einem Datumsbereich filtern
Beim Datum kommt es auf das Format des Kriteriums an. Ein Datum als Text wie ">=19.08.2024" funktioniert nur, wenn die Ländereinstellung des Rechners dazu passt. Sicher ist die interne Datumszahl, die CLng liefert:
Sub FilterDatumVonBis()
Dim ws As Worksheet
Dim datVon As Date, datBis As Date
Set ws = ThisWorkbook.Worksheets("Daten")
datVon = DateSerial(2024, 8, 19) 'oder aus einer Zelle: ws.Range("H1").Value
datBis = DateSerial(2024, 8, 25)
ws.Range("A1").CurrentRegion.AutoFilter Field:=3, _
Criteria1:=">=" & CLng(datVon), Operator:=xlAnd, _
Criteria2:="<" & CLng(datBis + 1) 'bis einschließlich datBis, auch mit Uhrzeit
End Sub
In einem deutschen Excel liefert das Textkriterium ">=19.08.2024" das richtige Ergebnis, ">=08/19/2024" dagegen keinen einzigen Treffer. Mit CLng stimmt das Ergebnis unabhängig von den Ländereinstellungen, sofern die Spalte echte Datumswerte enthält und keine Texte, die nur wie ein Datum aussehen. Die Obergrenze „kleiner als der Folgetag“ schließt auch Einträge am letzten Tag ein, die eine Uhrzeit enthalten, etwa 25.08.2024 12:00.
Gefilterte Daten auslesen
Mit einer Formel (Excel für Microsoft 365)
Sollen die sichtbaren Werte in einer Zelle stehen, brauchen Sie in aktuellen Excel-Versionen kein Makro. Diese Formel listet alle Artikel, die der Filter gerade anzeigt:
=TEXTVERKETTEN(" - ";WAHR;EINDEUTIG(FILTER(A2:A21;TEILERGEBNIS(103;BEREICH.VERSCHIEBEN(A2;ZEILE(A2:A21)-ZEILE(A2);0))=1;"")))
TEILERGEBNIS mit der Funktionsnummer 103 zählt nur sichtbare Zellen. Zusammen mit BEREICH.VERSCHIEBEN entsteht so für jede Zeile eine 1 (sichtbar) oder 0 (ausgefiltert), nach der FILTER die Liste aussiebt. EINDEUTIG entfernt doppelte Werte wie die VBA-Funktion unten, TEXTVERKETTEN überspringt mit WAHR leere Texte.
Mit einer eigenen Funktion (alle Versionen)
Für ältere Excel-Versionen leistet die benutzerdefinierte Funktion FILTERERGEBNIS dasselbe. Doppelte Werte erscheinen nur einmal, Fehlerwerte wie #DIV/0! werden übersprungen:
Option Explicit
Public Function FILTERERGEBNIS(ByVal Bereich As Range, Optional ByVal Trenner As String = ", ") As String
Dim zelle As Range
Dim werte As Object
Application.Volatile 'neu berechnen, wenn sich der Filter ändert
Set werte = CreateObject("Scripting.Dictionary")
For Each zelle In Bereich.Cells
If Not zelle.EntireRow.Hidden Then
If Not IsError(zelle.Value) Then 'Fehlerwerte überspringen
If Len(CStr(zelle.Value)) > 0 Then werte(CStr(zelle.Value)) = True
End If
End If
Next zelle
FILTERERGEBNIS = Join(werte.Keys, Trenner)
End Function
In der Tabelle verwenden Sie sie wie jede andere Funktion: =FILTERERGEBNIS(A2:A21;" - ").
Gefilterte Zeilen kopieren
Um nur die sichtbaren Zeilen auf ein anderes Blatt zu übertragen, kopiert SpecialCells(xlCellTypeVisible) genau den sichtbaren Teil der Liste:
Sub GefilterteZeilenKopieren()
Dim liste As Range
Set liste = ThisWorkbook.Worksheets("Daten").Range("A1").CurrentRegion
liste.SpecialCells(xlCellTypeVisible).Copy _
Destination:=ThisWorkbook.Worksheets("Auswertung").Range("A1")
End Sub
Filter aufheben oder entfernen
Sub FilterAufheben()
With ThisWorkbook.Worksheets("Daten")
If .FilterMode Then .ShowAllData 'alle Daten anzeigen, Filterpfeile bleiben
End With
End Sub
Sub FilterEntfernen()
ThisWorkbook.Worksheets("Daten").AutoFilterMode = False 'Autofilter samt Pfeilen entfernen
End Sub
Die Abfrage If .FilterMode ist wichtig: Ohne aktiven Filter löst ShowAllData einen Laufzeitfehler aus.
Autofilter trotz Blattschutz
Für das reine Filtern brauchen Sie kein Makro. Richten Sie den Autofilter ein, wählen Sie dann Überprüfen, Blatt schützen und setzen Sie den Haken bei „AutoFilter verwenden“. Anwender können danach filtern, aber keine Inhalte ändern.
Ein Makro brauchen Sie, wenn zusätzlich die Gruppierung (Gliederung) nutzbar bleiben soll oder wenn Ihre eigenen Makros auf dem geschützten Blatt arbeiten müssen. Dafür setzen Sie den Schutz mit UserInterfaceOnly. Weil Excel diese Einstellung beim Schließen vergisst, gehört der Code in das Ereignis Workbook_Open im Modul DieseArbeitsmappe:
Private Sub Workbook_Open()
With ThisWorkbook.Worksheets("Daten")
.Protect Password:="IhrKennwort", UserInterfaceOnly:=True, AllowFiltering:=True
.EnableAutoFilter = True 'Filterpfeile bedienbar
.EnableOutlining = True 'Gruppierung auf- und zuklappbar
End With
End Sub
Speichern Sie die Datei als Arbeitsmappe mit Makros (.xlsm). UserInterfaceOnly schützt das Blatt nur gegen Eingaben von Hand. Ihre Makros dürfen weiter schreiben und filtern, ohne den Schutz jedes Mal aufzuheben.
Passende Beiträge
Lieber machen lassen?
Wir programmieren die Lösung für Ihre Datei.
Vom einzelnen Makro bis zur Anwendung für die ganze Abteilung. Mit KI-Unterstützung setzen wir vieles schneller und günstiger um, jede Lösung testen wir vor der Übergabe.
