Excel Filterkriterien per VBA auslesen

Sebbl1990

Cadet 4th Year
Dabei seit
Okt. 2008
Beiträge
104
#1
Hallo zusammen,

ich habe ein Problem im Excel:

Ich habe eine Tabelle, in der ersten Zeile steht die Überschrift, darunter die Daten. In der Überschrift ist die klassische Filterfunktion von Excel aktiviert, sodass man die Daten entsprechend filtern kann. Soweit alles Standardfunktionen, nichts besonderes, tut auch soweit und soll wegen Benutzerfreundlichkeit nicht geändert werden.

Nun möchte ich mittels VBA die gesetzten Filter in einer bestimmten Spalte auslesen. Dazu gibt es im WWW auch zahlreiche Lösungsvorschläge (Criteria1 / Criteria2-Befehle...). Diese funktionieren auch, aber nur solange max. 2 Werte zum Filtern angewählt sind. Sobald ein 3. Wert als Filterkriterium angewählt ist und man das Makro ausführt, wirft Excel eine Fehlermeldung:

Laufzeitfehler 13 - Typen unverträglich.

Muss doch möglich sein, eine beliebig lange Liste mit Filterkriterien erstellen zu können?

Fiktives Beispiel:
In Spalte A "Nachnamen" sind 10 Einträge mit verschiedenen Nachnamen
Filtere ich nach "Maier" tut es und ich bekomme den Namen ausgegeben.
Filtere ich nach "Maier" und "Müller" tut es auch und ich bekomme beide Namen ausgegeben.
Filtere ich nach "Maier", "Müller" und "Hinz" tut es nicht mehr und ich bekomme nur die Fehlermeldung ausgegeben...

Hoffe auf Tipps, vielen Dank schon mal,
Sebastian
 
Dabei seit
Apr. 2012
Beiträge
7.224
#2
Ein Bild würde mehr sagen als 100 Worte.
Der Makrocode der scheitert, die Filterregel an de er scheitert. Oder eine Testmappe mit entsprechende Zuständen und Makro hier hochladen.

CN8
 

xdave78

Lt. Commander
Dabei seit
Juli 2011
Beiträge
1.262
#3
Warum "," statt "UND"?
"," ist kein logischer Operator.
 

Sebbl1990

Cadet 4th Year
Ersteller dieses Themas
Dabei seit
Okt. 2008
Beiträge
104
#4
Hallo,

hier mal eine Beispieldatei:

Den Anhang Filter_Beispiel.xlsx betrachten

Ich weiß nicht, ob das Makro mitgespeichert wird... xlsm kann man im Forum nicht hochladen.

Hier der Makrocode:

Code:
Sub Filter()
    Dim WS As Worksheet
    Dim flt As Filter
    Dim i As Integer

    'Variable anpassen.
    Set WS = Worksheets("Tabelle1") 'Tabellenblatt
    i = 1 '1. Spalte des Autofilters
    
    'Durchlaufen aller aktiven Filter
    For Each flt In WS.AutoFilter.Filters
        If flt.On = True Then
            Debug.Print "Spaltennr: " & i
            Debug.Print "============="
            Debug.Print "Kriterium1: " & flt.Criteria1
            If flt.Operator Then
                Debug.Print "Kriterium2: " & flt.Criteria2
            Else
                Debug.Print "Kriterium2: nicht vorhanden"
            End If
            Debug.Print
        End If
        i = i + 1
    Next flt
End Sub
Unbenannt.PNG
 
Dabei seit
Apr. 2012
Beiträge
7.224
#5
Was soll uns diese If/Else-Konstruktion sagen? Der Code frühstückt nur 2 Kriterien ab.
Wo kommt so ein Code denn her?

Und noch eine Bitte: so einen Link zu einer Datei wird hier niemand klicken. Benutze die Mittel des Forums.

CN8
 

Sebbl1990

Cadet 4th Year
Ersteller dieses Themas
Dabei seit
Okt. 2008
Beiträge
104
#6
Der Code ist ausm Netz. Ich habe eine Reihe von ähnlichen gefunden, die aber alle an der gleichen Stelle kranken - ab dem 3. Filterkriterium fliegt der Code mit Fehlermeldung um die Ohren.

Criteria1 und Criteria2 gibt das erste und ggf. zweite Filterkriterium zurück. Ein Criteria3 beherrscht Excel aber nicht. Eine Erweiterung des Codes damit funktioniert also nicht.
Da muss es doch aber eine andere Möglichkeit geben einen dritten, vierten etc. Wert auszulesen.


Die Exceldatei ist ein ganz normales Dateiattachement der Forenfunktion hier. Kein externer Link.
 

Janush

Lt. Junior Grade
Dabei seit
Mai 2008
Beiträge
377
#7
Moinsn,

weil‘s mich interessiert und ich nicht glauben konnte, dass es so schwierig sein soll mehr als 2 Filterkriterien ausgeben zu lassen. Normalerweise ist ja bei Excel und Co. alles in Collection Objekten organisiert.

Wie es aber aussieht, ist es doch nicht ganz so einfach :-)

Also, legen wir mal los:
Zuallererst gehen wir davon aus, dass es auf der Tabelle „Tabelle1“ nur maximal einen Autofilter gibt. Auf diesen greifen wir durch…

Set WS = ThisWorkbook.Sheets.Item(1)
With WS.AutoFilter.Filters.Item(1)

…zu.

Hier ist es nun so, dass bei 1 oder 2 gesetzten Kriterien, .Critirea1 und .Criteria2 mit diesen Kriterien belegt sind. Kann man einfach durch msgbox .Criteria1 ausgeben lassen. Soweit so gut…

Jetzt kommst: Aus irgendeinem Grund, wird diese Logik ab dem 3. Kriterium über den Haufen geworfen und .Criteria1 gibt ALLE Filterkriterien auf einmal aus und zwar in einem Variant-Array. Das haben wohl die Seiten im Netz einfach mal unter den Tisch fallen lassen…wird schon keiner danach fragen…lol.

Was wir also brauchen ist eine Variant Variable in welche alle Kriterien geladen werden, um diese dann durch eine FOR Schleife schicken zu können. Allerdings nur, wenn mehr als 2 Kriterien gesetzt wurden, da sonst das Ganze nicht mehr funktioniert.

Hier ist der Code dazu:

Code:
Option Explicit

Sub Filter()
    Dim WS As Worksheet
    Dim i As Integer
    
    'Hier kommen am Ende alle unsere Filterkriterien rein
    'Wird am Ende ein Array
    'Aber nur wenn mehr als 2 Kriterien existieren
    Dim filterCriteria As Variant
 
    Set WS = ThisWorkbook.Sheets.Item(1) 'Tabellenblatt
    
    'Check ob Filter aktiv
    If WS.AutoFilter Is Nothing Then MsgBox "Kein Filter aktiv!": Exit Sub
    
    'Check ob mindestens 1 Filterkriterium ausgewaehlt wurde
    If Not WS.AutoFilter.FilterMode Then MsgBox "Filter all!": Exit Sub
    
    'Gibt nur einen Filter in Tabelle1
    With WS.AutoFilter.Filters.Item(1)
        If .Count < 3 Then
            'Erzeugen eines Variant Arrays
            'mit der Anzahl der Kriterien
            '1 oder 2
            ReDim filterCriteria(.Count)
            
            'Kriterium 1 existiert auf jeden Fall
            filterCriteria(1) = .Criteria1
            
            'Wenn mehr als 1 aber weniger als 3
            If .Count = 2 Then filterCriteria(2) = .Criteria2
        Else
            'Wenn mehr als 2
            'Uebergabe aller Kriterien in ein Variant Array
            filterCriteria = .Criteria1
        End If
        
        For i = 1 To UBound(filterCriteria)
            'Durchlaufen aller Kriterien und Ausgabe
            'als Messagebox
            'Hier kann man auch code fuer Zellen-Ausgabe
            'einbauen
            MsgBox filterCriteria(i)
        Next i
    End With
    
Set WS = Nothing
End Sub
Viel Spass beim basteln :-)

Edit:
Ach ja, am Ende wird nur eine Variant Variable ausgegeben und deswegen laed dieses Makro bei 1 oder 2 gesetzen Kriterien diese auch in ein Variant Array. Ich hoffe das ergibt Sinn :-)
 
Zuletzt bearbeitet:

Sebbl1990

Cadet 4th Year
Ersteller dieses Themas
Dabei seit
Okt. 2008
Beiträge
104
#8
Hallo Janush,

vielen Dank! Ich habe es gerade an der Beispieldatei ausprobiert und es scheint zu funktionieren, klasse! :)

Werde ich morgen oder die nächsten Tage probieren in meine eigentliche Datei zu implementieren, sollte aber das kleinere Problem sein.

Gut, dass einer der sich auskennt, bestätigt, dass diese Filterkriterien nicht ganz einfach und offensichtlich auch etwas unlogisch zu handhaben sind. Hatte schon an mir gezweifelt. ;)

Dann hoffe ich mal, dass Leute mit ähnlichem Problem auf diesen Thread stoßen werden...

Gruß
Seb
 
Dabei seit
Apr. 2012
Beiträge
7.224
#9
Ich hatte auch auf das .count gesetzt.

Da ich AutoFilter nicht zu meinen Freunden zähle bin ich da unbedarft, aber mich wundern die (zwei) Schlüsselworte .criteria1 und .criteria2. Gibt es da nicht konsequent einen Index? Die Zuweisung in ein Array ist schon der Hammer.

CN8
 

Janush

Lt. Junior Grade
Dabei seit
Mai 2008
Beiträge
377
#10
Man würde tatsächlich erwarten, dass es da, wo es ein .Count gibt, auch ein .Items geben würde. Aus irgendeinem Grund ist es hier aber anders.

Ich habe auch noch nicht probiert, was .Criteria2 zurück gibt, wenn mehr als zwei Kriterien gesetzt werden.

Auf jeden Fall interessant :-)

Edit: Habs ausprobiert und .Criteria2 bleibt bei mehr als 2 Kriterien leer...
 
Zuletzt bearbeitet:

Sebbl1990

Cadet 4th Year
Ersteller dieses Themas
Dabei seit
Okt. 2008
Beiträge
104
#11
Hallo nochmal,

also die Logik funktioniert wie gesagt, allerdings bekomme ich es in meinem eigentlichen File leider noch nicht ans Laufen.

Da ist die Tabelle nicht beginnend mit Zelle A1 und es kann passieren, dass mehrere Filter gesetzt sind. Auslesen möchte ich aber immer eine bestimmte Spalte der Tabelle. Und ich scheitere schon daran, das Tabellenblatt zu definieren, in welchem das Auslesen geschehen soll. :freak: Sheets durch Worksheets("Tabellenname") in Zeile 12 zu ersetzen funktioniert nicht.
Ich vermute zwar, dass er im richtigen Tabellenblatt arbeitet, kann es allerdings nicht prüfen. Und es kommt immer die Ausgabe "Filter all". Er erkennt also, dass Autofilter aktiv sind, aber erkennt nicht, dass auch Filterkriterien tatsächlich angehakt sind.

Wenn gewünscht kann ich nochmal ein ausführlicheres Beispielfile erstellen. Hätte nicht gedacht, dass ich an der Stelle jetzt scheitere. :rolleyes:

Danke und Gruß
Sebastian
 

Janush

Lt. Junior Grade
Dabei seit
Mai 2008
Beiträge
377
#12
Aus

Set WS = ThisWorkbook.Sheets.Item(1)

machst du einfach

Set WS = ThisWorkbook.Sheets("Tabellenname")

das sollte funktionieren. Wenn du mehrere Filter gesetzt hast, muss du tatsaechlich mit einer FOR EACH Schleife "durch loopen". Kannst ja mal ein Beispiel fuer uns hochladen.
 
Dabei seit
Apr. 2012
Beiträge
7.224
#13
Ich mag Namen (oder eben Label) eigentlich lieber als Nummern, aber ein Zählindex ist da und dort schon ganz nett :D

Warum aber der Verweis auf den Tab #1 - .Item(1) oder direkt (1) sollten doch entsprechen!? - nicht geht erstaunt mich, sofern "Tabellenname" der Label des 1. Blatts wäre.

Excel ist wohl immer für eine Überraschung gut. Vielleicht muss ich da doch mal experimentieren um mit solchen Seltsamkeiten Umgang zu bekommen.

CN8
 

Sebbl1990

Cadet 4th Year
Ersteller dieses Themas
Dabei seit
Okt. 2008
Beiträge
104
#14
Habe es doch hinbekommen - unten mal ein leicht abgeänderter Code.

Zugriff aufs gewünschte Tabellenblatt geht wie von Janush beschrieben.
Und mit "With WS.AutoFilter.Filters.Item(*Spaltennr. des zu untersuchenden Filters*)" greift man eine bestimmte Spalte aus der gefilterten Tabelle raus.
Loopen muss ich jetzt noch nicht mal, da ich immer nur auf eine bestimmte Spalte schaue.

Danke und mal sehen, wann das nächste unüberwindbare Problem auftaucht. :D
Sebastian

Code:
Option Explicit

Sub Filter()
    Dim WS As Worksheet
    Dim i As Integer
    
    'Hier kommen am Ende alle unsere Filterkriterien rein
    'Wird am Ende ein Array
    'Aber nur wenn mehr als 2 Kriterien existieren
    Dim filterCriteria As Variant
 
    Set WS = ThisWorkbook.Sheets("[B]Tabellenname[/B]") 'Tabellenblatt
    
    'Check ob Filter aktiv
    If WS.AutoFilter Is Nothing Then MsgBox "Kein Filter aktiv!": Exit Sub
    
    'Check ob mindestens 1 Filterkriterium ausgewaehlt wurde
    If Not WS.AutoFilter.FilterMode Then MsgBox "Filter all!": Exit Sub
    
    'Gibt nur einen Filter in Tabelle1
    With WS.AutoFilter.Filters.Item([B]*Spaltennr. des zu untersuchenden Filters*[/B])
        If .Count < 3 Then
            'Erzeugen eines Variant Arrays
            'mit der Anzahl der Kriterien
            '1 oder 2
            ReDim filterCriteria(.Count)
            
            'Kriterium 1 existiert auf jeden Fall
            filterCriteria(1) = .Criteria1
            
            'Wenn mehr als 1 aber weniger als 3
            If .Count = 2 Then filterCriteria(2) = .Criteria2
        Else
            'Wenn mehr als 2
            'Uebergabe aller Kriterien in ein Variant Array
            filterCriteria = .Criteria1
        End If
        
        For i = 1 To UBound(filterCriteria)
            'Durchlaufen aller Kriterien und Ausgabe
            'als Messagebox
            'Hier kann man auch code fuer Zellen-Ausgabe
            'einbauen
            MsgBox filterCriteria(i)
        Next i
    End With
    
Set WS = Nothing
End Sub
 

Janush

Lt. Junior Grade
Dabei seit
Mai 2008
Beiträge
377
#15
Gute Idee, da hätten wir aber auch früher drauf kommen können :-)

Jetzt darf sich halt nur nicht die Anzahl der Filter, VOR dem eigentlichen Filter, ändern.
 
Top