Autofilter per VBA: Kriterien, Datum, Auslesen, Blattschutz

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.