Autofilters in Excel 2007 en hoger

  • 16/10/2016
Voorbeeldbestanden bij dit artikel:
  • Autofilter 2007 VBA
  • Autofilter 2007 VBA

    • Inleiding

    Reeds lang kennen Excel gebruikers de mogelijkheden van een autofilter. Dat laat ons toe om tabellen te filteren op 1 of meerdere waarden in 1 of meerdere kolommen. Dit is een zeer handig hulpmiddel bij de analyse van (grote hoeveelheden) gegevens.

    De tabel die ik zal gebruiken in dit artikel is hieronder afgebeeld. Merk op dat het een mooi aansluitende tabel is zonder lege regels of kolommen, met titelhoofdingen en slechts 1 type gegevens per kolom. Dit vergemakkelijkt de analyse.

    Autofilter in Excel 2007

    Sinds de introductie van Excel 2007 zijn de mogelijkheden van autofilters echter drastisch uitgebreid. En daarover gaat dit artikel. We bekijken niet zozeer de mogelijkheden in de gebruikersinterface, ik denk dat dat wel voor zich spreekt. We gebruiken nog steeds de filterpijlen bovenaan elke kolom, maar daarbinnen zijn er veel meer opties:

    • tijdsgebonden filters gebruiken
    • meerdere waarden kiezen om te filteren
    • filteren op kleur
    • filteren met iconen
    • …

    VBA-code

    Over het algemeen wijst het zichzelf wel uit. In dit artikel bespreken we de VBA-code voor autofilters in Excel 2007 (en vermoedelijk ook hogere versies). Naar het einde van het artikel toe, ga je merken dat bij VBA nog niet alles op punt staat voor deze vernieuwingen. Ik zal 2 concrete zaken bespreken. Dit artikel zal vele voorbeeldcodes tonen om de discussie concreet te maken. Beginnen doen we zoals gewoonlijk bij het begin. Willen we de 2de kolom filteren op "zij", dan nemen we: 

    Sub Autofilter_1()
    
    Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:="zij"


    End Sub

    Moet het bedrag (veld 5) liggen tussen 100 en 500 (grenzen inbegrepen), dan wordt het: 

    Sub Autofilter_2()
    
    Range("A1").CurrentRegion.AutoFilter Field:=5, Criteria1:=">=100", Operator:=xlAnd, Criteria2:="<=500"


    End Sub

    Willen we zowel op kolom 2 als 5 filteren, dus voorgaande filters combineren: 

    Sub Autofilter_3()
    
    With Range("A1").CurrentRegion
        .AutoFilter Field:=2, Criteria1:="zij"
        .AutoFilter Field:=5, Criteria1:=">=100", Operator:=xlAnd, Criteria2:="<=500"
    End With


    End Sub

    Dezelfde procedure kan ook op een kortere manier geschreven worden. We laten de namen van de argumenten voor de AutoFilter methode weg: 

    Sub Autofilter_4()
    
    With Range("A1").CurrentRegion
        .AutoFilter 2, "zij"
        .AutoFilter 5, ">=100", xlAnd, "<=500"
    End With


    End Sub

    Je kan filteren met wildcards. Een asterisk (*) staat voor 1 of meerdere karakters, een ? staat voor 1 karakter: 

    Sub Autofilter_5()
    
    Range("A1").CurrentRegion.AutoFilter 2, "j*"

    'of:

    Range("A1").CurrentRegion.AutoFilter 2, "?ij"


    End Sub

    Interessanter wordt het als je kan filteren op basis van de inhoud van cellen. Hier is dit met cellen B24 en C24 voorgedaan: 

    Sub Autofilter_6()
    
    With Range("A1").CurrentRegion
        .AutoFilter 2, "zij"
        .AutoFilter 5, ">=" & Range("B24").Value, xlAnd, "<=" & Range("C24").Value
    End With


    End Sub

    Tot slot kunnen we ook makkelijk filteren op de niet-lege cellen (er zijn geen lege cellen in de tabel): 

    Sub Autofilter_7()
    
    Range("A1").CurrentRegion.AutoFilter 1, "<>"


    End Sub

    Verander “<>” in “=” voor het filteren op lege cellen.

    Op dit moment hebben we nog niets specifiek voor Excel 2007 gezien. Dit alles is de standaard autofilter die ook in vorige versies voorhanden was en zo geprogrammeerd kon worden. Laten we nog even de mogelijkheden op een rij zetten om de filter ongedaan te maken: 

    Sub Autofilter_UIT_1()
    
    'volledig uit - geen pijlen meer
    Range("A1").CurrentRegion.AutoFilter


    End Sub
    


    Sub Autofilter_UIT_2()
    
    'volledig uit - geen pijlen meer
    ActiveSheet.AutoFilterMode = False


    End Sub
    


    Sub Autofilter_UIT_3()
    
    'alle records laten zien - pijlen blijven
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData


    End Sub

    Wordt vervolgd…

    Op pagina 2 van dit artikel zoomen we in op een aantal nieuwigheden in de autofilter VBA voor Excel 2007. Hier ronden we af met een aantal opmerkingen:

    • beperk jezelf niet tot een autofilter, een uitgebeide filter is ook een zeer krachtig instrument
    • VBA-code voor het verwijderen, kopiëren en aanpassen van gegevens maakt heel vaak gebruik van een autofilter
    • gebruik ook de macro recorder als je niet zeker bent hoe je bepaalde filteracties moet omzetten in code
    • de codes in dit artikel gaan uit van bereiken op het actieve werkblad; indien het om andere werkbladen gaat moet dit in de codes aangepast worden.




    Homepage

    Rubriek onderdelen

    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links