Excel Filterkriterien per VBA auslesen

[Sebbl1990]

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

 

[cumulonimbus8]

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

 

[AwesomSTUFF]

Warum "," statt "UND"?
"," ist kein logischer Operator.

 

[Sebbl1990]

Hallo,

hier mal eine Beispieldatei:

Anhang anzeigen Filter_Beispiel.xlsx

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

Hier der Makrocode:

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

 

[cumulonimbus8]

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]

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]

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:

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 :-)

 

[Sebbl1990]

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

 

[cumulonimbus8]

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]

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...

 

[Sebbl1990]

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. 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.

Danke und Gruß
Sebastian

 

[Janush]

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.

 

[cumulonimbus8]

Ich mag Namen (oder eben Label) eigentlich lieber als Nummern, aber ein Zählindex ist da und dort schon ganz nett

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]

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.
Sebastian

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]

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.

 

[gehtnetgibtsnet]

Hallo und "grüß Gott". Ich bin neu in diesem Forum, weil ich auf der Suche nach einer Lösung das oben stehende Beispiel gefunden habe.

Ich habe in einer Tabelle einen Filter eingerichtet, und dort mehrere Worte ausgewählt. Der oben dargestellte Code hat mir dann genau die ausgewählten Wörter wieder angezeigt.

Mein Problem jedoch sind nicht verschiedene Wörter, sondern das Filtern nach Farben. Deshalb habe ich eines der Wörter mit gelber Hintergrundfarbe hinterlegt, und im Filter "nach Farbe filtern" ausgewählt. Da nur ein Feld farblich hinterlegt ist, wurde mir auch nur die gelbe Farbe zur Filter-Auswahl angeboten.

Wenn ich nun den oben dargestellten Code ausführe, erhalte ich einen Laufzeitfehler 438 bei der folgenden Zeile:

'Kriterium 1 existiert auf jeden Fall
filterCriteria(1) = .Criteria1

Meine Frage: wie kann ich die Farb-Informationen aus dem Filter auslesen?

Gruß gngn

 

[cumulonimbus8]

Spontaner Gedanke: Ist so eine Farbe etwas auf das ein Autofilter regaieren kann..?

CN8

 

[gehtnetgibtsnet]

Hallo CN8.

Noch nie probiert?

Gruß gngn

 

[Janush]

Hey Mate,

hier ist ein kleiner Überblick über das Autofilter-Objekt. Wenn du also an Criteria1 noch ein .Color anhängst gibt dir das Makro auch den Farbcode aus. Das Ganze als Wort zu erhalten wird wahrscheinlich schwierig.

Du musst halt schauen, wie du das jetzt mit dem Rest des Makros integrieren kannst, damit es auch die richtige Information ausgibt :-)

 

[gehtnetgibtsnet]

Hallo Janush,

vielen Dank für Deine Antwort.

Zuerst interessiert mich natürlich, wie ich zu der von Dir erstellten Übersicht gelange. Ich habe es bisher nicht herausgefunden. Meine VBA-Kenntnisse reichen dazu nicht aus.

Wenn ich dann die Informationen zu meiner eigenen Filterliste habe, wird mir (vermutlich) einiges verständlicher.

Gruß
gngn