Autofilters in Excel 2007 en hoger

  • 16/10/2016

Filteren op meerdere waarden

De voorlaatste pagina van dit artikel gaat uitleggen hoe we meerdere waarden tegelijk kunnen instellen in de autofilter. Dit was absoluut onmogelijk in versies vóór Excel 2007. Excel 2007 toont ons een overzicht van de unieke waarden in een kolom, waarbij wij dan onze keuzes kunnen aankruisen. Zonder code zou je zo te werk gaan, als we moeten filteren op Persoon bijvoorbeeld:

Hier heb ik gefilterd op de personen "jij" en "hij". Excel toont ons een alfabetisch gesorteerde lijst van de unieke waarden. In een kolom met getallen zou die oplopend gesorteerd zijn en de unieke bedragen tonen. In een kolom met datums zou je een overzicht van de unieke datums krijgen, geordend per maand en jaar. Dat is een zeer grote vooruitgang en bevordert het gebruiksgemak. Ietwat raar is dat er bij een kolom met tijdstippen geen ingebouwde herkenning gebeurt en dat er geen tijdsschalen van 1 uur of een 30 minuten aangeboden worden.

Ik heb de waarden "jij" en "hij" ook onder de gegevenstabel geplaatst, omdat we de filter ook met VBA willen doen. Kijk naar de cellen B28:C28 (horizontaal) en B30:B31 (verticaal) en dan deze 2 codeblokjes: 

Sub Autofilter_10a()

    Range("A1").CurrentRegion.AutoFilter _
        Field:=2, _
        Criteria1:=Range("B28:C28").Value, _
        Operator:=xlFilterValues


End Sub


Sub Autofilter_10b()

    Range("A1").CurrentRegion.AutoFilter _
            Field:=5, _
            Criteria1:=Split(Join(Application.Transpose(Application.Transpose(Range("B24:C24").Value)))), _
            Operator:=xlFilterValues


End Sub

Bij het filteren met een array heb je String waarden (tekstwaarden) nodig. Wil je m.a.w. filteren op getallen zoals in code blokje 10b dan moeten we de getallen 311 en 283 in de array eerst omzetten naar "311" en "283" als tekst. En bovendien moet kolom E (waarop we filteren) enkel de niet-opgemaakte getallen bevatten. Zo zullen 311 en 283 via de array niet gevonden worden als de notatie is: ? 311 en ? 283. 

Sub Autofilter_11()

    Range("A1").CurrentRegion.AutoFilter _
        Field:=2, _
        Criteria1:=Application.Transpose(Range("B30:B31").Value), _
        Operator:=xlFilterValues


End Sub

Belangrijk om in te zien, is dat de operator hier anders is: het is geen xlFilterDynamic of xlFilterCellColor meer, maar een xlFilterValues. Die laat de selectie van meerdere waarden toe.

Wat dus perfect mogelijk is, is dat je via code eerst bepaalt op welke waarden er gefilterd moet worden. Zolang je dit aanbrengt aan VBA in de juiste vorm, zal dit werken. 2 voorbeelden voor de landen (merk op: de filterwaarden bevinden zich hardgecodeerd in de VBA-code, en nergens in het werkblad): 

Sub Autofilter_12a()

    Range("A1").CurrentRegion.AutoFilter _
        Field:=1, _
        Criteria1:=Array("Frankrijk", "België", "Duitsland"), _
        Operator:=xlFilterValues


End Sub


Sub Autofilter_12b()

    Range("A1").CurrentRegion.AutoFilter _
        Field:=1, _
        Criteria1:=Split("Frankrijk#België#Duitsland", "#"), _
        Operator:=xlFilterValues


End Sub

Indien de landen geen spaties bevatten, zal dit ook werken: 

Sub Autofilter_12b_herwerkt()

    Range("A1").CurrentRegion.AutoFilter _
        Field:=1, _
        Criteria1:=Split("Frankrijk België Duitsland"), _
        Operator:=xlFilterValues


End Sub

Wilde je dit soort dingen doen in Excel 2003, dan was je bijna verplicht een kolom extra te gebruiken in de tabel, daar een AANTAL.ALS functie, en vervolgens filteren op de cellen groter dan 0. Kunnen we ook het omgekeerde doen: uitvragen waarop wordt gefilterd? Ja: 

Sub Autofilter_13()

    Dim s As String

    With ActiveSheet.AutoFilter.Filters(1)

        If .On Then

            Select Case .Operator
            Case 0
                s = Replace("#" & .Criteria1, "#=", "")
            Case 2 'xlOr
                s = Replace("#" & .Criteria1 & "#" & .Criteria2, "#=", vbCr)
            Case 7 'xlFilterValues
                s = Replace("#" & Join(.Criteria1, "#"), "#=", vbCr)
            End Select

            If Len(s) Then MsgBox s, vbInformation, "Selected value(s)"

        End If

    End With


End Sub

Het resultaat is een MsgBox in dit geval, maar zou evenzo in een cel weggeschreven kunnen worden als we de procedure omvormen tot een UDF (User Defined Function of GebruikersGedefinieerde functie).

De selectie omdraaien

We sluiten deze pagina af met een leuke toevoeging. In Autofilter 10 hebben we de personen gefilterd op "jij" en "hij". Is het mogelijk om de selectie om te draaien? We hebben 6 personen in totaal, we starten met 2 gekozen personen, dus de macro moet dan filteren op de 4 overige personen. De namen van de 4 personen zetten we niet in het werkblad - in een groot bestand zou het kunnen gaan om duizenden regels en dito waarden. Hier is een interessante manier om dit te doen: 

Sub Autofilter_14()

    Autofilter_10

    Selectie = Application.Transpose(Range("B30:B31").Value)
    AlleWaarden = Application.Transpose(Range("A1").CurrentRegion.Columns(2).Value)

    InverseSelectie = AlleWaarden
    For i = 1 To UBound(Selectie)
        InverseSelectie = Filter(InverseSelectie, Selectie(i), False, vbTextCompare)
    Next

    Range("A1").CurrentRegion.AutoFilter 2, InverseSelectie, xlFilterValues


End Sub

Eerst stel ik de filter in uit de procedure Autofilter_10. Vervolgens halen we de selectie op van 2 personen (dit had ook gekund met code vergelijkbaar met procedure Autofilter_13) en tevens de lijst van alle personen in de tabel (niet uniek). Dan halen we in een lus de 2 geselecteerde namen uit de totaallijst. Tot slot filteren we de kolom Persoon op de uitgezuiverde lijst van personen: de unieke waarden zijn de 4 overige personen, maar die komen elk x aantal keer voor. Dit is ook niet belangrijk ten aanzien van de autofilter.

Wordt vervolgd…

Zo, hier zijn we aan het einde van de bespreking van de Autofilter voor VBA in Excel 2007. Ik hoop dat ik onderweg niet al teveel lezers ben verloren. Op pagina 5 staan er nog 2 observaties die ik maakte bij het opzetten van deze pagina en het vele uitzoekwerk.

Homepage

Rubriek onderdelen

Over Wim

Wim Gielis is Business Intelligence consultant en Excel expert

Andere links