Excel VBA Autofilter: A Complete Guide with Examples

Er zijn veel Excel-functionaliteiten die ook in VBA kunnen worden gebruikt – en de Autofilter-methode is zo’n functionaliteit.

Als u een dataset hebt en u wilt die filteren aan de hand van een criterium, dan kunt u dat eenvoudig doen met de optie Filter in het Gegevenslint.

En als u er een meer geavanceerde versie van wilt, dan is er ook een geavanceerd filter in Excel.

Waarom dan nog het AutoFilter in VBA gebruiken?

Als u alleen gegevens hoeft te filteren en wat basisdingen moet doen, zou ik u aanraden om het bij de ingebouwde filterfunctionaliteit te houden die Excel-interface biedt.

U moet het VBA Autofilter gebruiken als u de gegevens wilt filteren als onderdeel van uw automatisering (of als het u helpt tijd te besparen door het filteren van de gegevens sneller te laten verlopen).

Stel bijvoorbeeld dat u snel de gegevens wilt filteren op basis van een vervolgkeuzeselectie en deze gefilterde gegevens vervolgens naar een nieuw werkblad wilt kopiëren.

Hoewel dit kan worden gedaan met behulp van de ingebouwde filterfunctionaliteit samen met wat kopiëren-plakken, kan het u veel tijd kosten om dit handmatig te doen.

In een dergelijk scenario kan het gebruik van VBA Autofilter de zaken versnellen en tijd besparen.

Note: Ik zal dit voorbeeld (over het filteren van gegevens op basis van een drop-down selectie en kopiëren naar een nieuw blad) later in deze tutorial behandelen.

Excel VBA Autofilter Syntax

Expression. AutoFilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )

• Expression: Dit is het bereik waarop u het autofilter wilt toepassen.
• Veld: Dit is het kolomnummer waarop u wilt filteren. Dit wordt geteld vanaf links in de dataset. Dus als u gegevens wilt filteren op basis van de tweede kolom, zou deze waarde 2 zijn.
• Criteria1: Dit is het criterium op basis waarvan u de dataset wilt filteren.
• Operator: In het geval dat u ook criteria 2 gebruikt, kunt u deze twee criteria combineren op basis van de Operator. De volgende operatoren zijn beschikbaar voor gebruik: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Criteria2: Dit is het tweede criterium waarop u de dataset kunt filteren.
• VisibleDropDown: U kunt opgeven of u wilt dat het filter drop-down pictogram in de gefilterde kolommen verschijnt of niet. Dit argument kan WAAR of ONWAAR zijn.

Behoudens Expression zijn alle andere argumenten optioneel.

In het geval dat u geen enkel argument gebruikt, worden de filterpictogrammen gewoon op de kolommen toegepast of verwijderd.

Sub FilterRows()Worksheets("Filter Data").Range("A1").AutoFilterEnd Sub

De bovenstaande code zou gewoon de Autofilter-methode op de kolommen toepassen (of als deze al is toegepast, wordt deze verwijderd).

Dit betekent gewoon dat als je niet kunt zien de filter pictogrammen in de kolomkoppen, zult u beginnen te zien wanneer deze bovenstaande code wordt uitgevoerd, en als je kunt zien, dan zal het worden verwijderd.

In het geval dat u gefilterde gegevens, zal het verwijderen van de filters en tonen u de volledige dataset.

Nu laten we eens kijken naar een aantal voorbeelden van het gebruik van Excel VBA Autofilter dat zal duidelijk maken het gebruik ervan.

Voorbeeld: Gegevens filteren op basis van een tekstvoorwaarde

Voorstel dat u een gegevensreeks hebt zoals hieronder afgebeeld en u wilt deze filteren op basis van de kolom ‘Item’.

Onderstaande code zou alle rijen filteren waarin het item ‘Printer’ is.

Sub FilterRows()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer"End Sub

De bovenstaande code verwijst naar Blad1 en daarbinnen naar A1 (een cel in de dataset).

Merk op dat we hier Field:=2 hebben gebruikt, omdat de kolom ‘Item’ de tweede kolom in onze dataset van links is.

Maar voordat ik u die laat zien, zal ik eerst een paar voorbeelden behandelen om u te laten zien wat de AutoFilter-methode allemaal kan doen.

Klik hier om het voorbeeldbestand te downloaden en volg mee.

Voorbeeld: Meerdere criteria (AND/OR) in dezelfde kolom

Stel dat ik dezelfde dataset heb, en deze keer wil ik alle records filteren waarbij het item ‘Printer’ of ‘Projector’ is.

De onderstaande code zou dit doen:

Sub FilterRowsOR()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector"End Sub

Merk op dat ik hier de xlOR-operator heb gebruikt.

Dit vertelt VBA om beide criteria te gebruiken en de gegevens te filteren als aan een van de twee criteria wordt voldaan.

Ook kunt u het EN-criterium gebruiken.

Als u bijvoorbeeld alle records wilt filteren waarvan het aantal meer dan 10 maar minder dan 20 is, kunt u de onderstaande code gebruiken:

Sub FilterRowsAND()Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20"End Sub

Voorbeeld: Meerdere criteria met verschillende kolommen

Stel dat u de volgende dataset hebt.

Met Autofilter kunt u meerdere kolommen tegelijk filteren.

U kunt bijvoorbeeld alle records filteren waarbij het item ‘Printer’ is en de verkoper ‘Mark’, dan kunt u de volgende code gebruiken:

Sub FilterRows()With Worksheets("Sheet1").Range("A1").AutoFilter field:=2, Criteria1:="Printer".AutoFilter field:=3, Criteria1:="Mark"End WithEnd Sub

Voorbeeld: Filter Top 10 Records Using the AutoFilter Method

Voorstel dat u de onderstaande dataset hebt.

Hieronder vindt u de code waarmee u de top 10 records krijgt (op basis van de kolom Aantal):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10ItemsEnd Sub

In de bovenstaande code heb ik ActiveSheet gebruikt. U kunt de naam van het blad gebruiken als u dat wilt.

Merkt u op dat u in dit voorbeeld, als u de top 5 items wilt krijgen, gewoon het getal in Criteria1:=”10″ verandert van 10 in 5.

Dus voor top 5 items zou de code zijn:

Sub FilterRowsTop5()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operator:=xlTop10ItemsEnd Sub

Het ziet er misschien vreemd uit, maar het maakt niet uit hoeveel top items u wilt, de Operator-waarde blijft altijd xlTop10Items.

Ook met de onderstaande code krijgt u de onderste 10 items:

Sub FilterRowsBottom10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10ItemsEnd Sub

En als u de onderste 5 items wilt, wijzigt u het getal in Criteria1:=”10″ van 10 in 5.

Voorbeeld: Filter Top 10 Percent Using the AutoFilter Method

Voorstel dat u dezelfde dataset hebt (zoals gebruikt in de vorige voorbeelden).

Hieronder staat de code die u de top 10 procent records zal geven (gebaseerd op de hoeveelheidskolom):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10PercentEnd Sub

In onze dataset, aangezien we 20 records hebben, zal het de top 2 records teruggeven (dat is 10% van het totaal aantal records).

Voorbeeld: Gebruik van jokertekens in autofilter

Stel dat u een dataset hebt zoals hieronder wordt weergegeven:

Als u alle rijen wilt filteren waarin de itemnaam het woord ‘Board’ bevat, kunt u de onderstaande code gebruiken:

Sub FilterRowsWildcard()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="*Board*"End Sub

In de bovenstaande code heb ik het jokerteken * (asterisk) gebruikt voor en na het woord ‘Board’ (dat de criteria is).

Een asterisk kan een willekeurig aantal tekens vertegenwoordigen. Dus dit zou elk item filteren waar het woord ‘board’ in voorkomt.

Voorbeeld: Gefilterde rijen naar een nieuw blad kopiëren

Als u niet alleen de records op basis van criteria wilt filteren, maar ook de gefilterde rijen wilt kopiëren, kunt u de onderstaande macro gebruiken.

Hij kopieert de gefilterde rijen, voegt een nieuw werkblad toe en plakt deze gekopieerde rijen vervolgens in het nieuwe blad.

Sub CopyFilteredRows()Dim rng As RangeDim ws As WorksheetIf Worksheets("Sheet1").AutoFilterMode = False ThenMsgBox "There are no filtered rows"Exit SubEnd IfSet rng = Worksheets("Sheet1").AutoFilter.RangeSet ws = Worksheets.Addrng.Copy Range("A1")End Sub

De bovenstaande code controleert of er gefilterde rijen in Blad1 staan of niet.

Als er geen gefilterde rijen staan, verschijnt er een berichtvenster waarin dat staat.

En als er wel gefilterde rijen staan, worden die gekopieerd, wordt er een nieuw werkblad ingevoegd en worden die rijen op dat nieuw ingevoegde werkblad geplakt.

Voorbeeld: Gegevens filteren op basis van een celwaarde

Met Autofilter in VBA in combinatie met een vervolgkeuzelijst kunt u een functionaliteit maken waarbij, zodra u een item uit de vervolgkeuzelijst selecteert, alle records voor dat item worden gefilterd.

Zoiets als hieronder wordt getoond:

Klik hier om het voorbeeldbestand te downloaden en mee te volgen.

Dit soort constructies kan nuttig zijn wanneer u snel gegevens wilt filteren en deze vervolgens verder in uw werk wilt gebruiken.

Hieronder staat de code die dit doet:

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = "$B" Then If Range("B2") = "All" Then Range("A5").AutoFilter Else Range("A5").AutoFilter Field:=2, Criteria1:=Range("B2") End IfEnd IfEnd Sub

Dit is een werkbladgebeurteniscode, die alleen wordt uitgevoerd als er een wijziging in het werkblad optreedt en de doelcel B2 is (waar we de vervolgkeuzelijst hebben).

Ook wordt er een Als Dan Else-conditie gebruikt om te controleren of de gebruiker ‘Alles’ heeft geselecteerd in de vervolgkeuzelijst. Als All is geselecteerd, wordt de volledige gegevensset getoond.

Deze code wordt NIET in een module geplaatst.

In plaats daarvan moet deze code worden geplaatst in de backend van het werkblad dat deze gegevens bevat.

Hier volgen de stappen om deze code in het werkblad code venster te plaatsen:

1. Open de VB-editor (sneltoets – ALT + F11).
2. In het Project Explorer deelvenster, dubbelklik op de naam van het werkblad waarin u deze filtering functionaliteit wilt.
3. Kopieer en plak bovenstaande code in het codevenster van het werkblad.
4. Sluit de VB Editor.

Als u nu de vervolgkeuzelijst gebruikt, worden de gegevens automatisch gefilterd.

Dit is een werkbladgebeurteniscode, die alleen wordt uitgevoerd als er een wijziging in het werkblad is en de doelcel B2 is (waar we de vervolgkeuzelijst hebben).

Ook wordt er een voorwaarde If Then Else gebruikt om te controleren of de gebruiker ‘Alles’ heeft geselecteerd in de vervolgkeuzelijst. Als All is geselecteerd, wordt de gehele dataset getoond.

Zet Excel AutoFilter aan / uit met behulp van VBA

Bij het toepassen van Autofilter op een reeks cellen, kan er al een aantal filters op zijn plaats zijn.

U kunt de onderstaande code gebruiken om alle vooraf toegepaste autofilters uit te schakelen:

Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = FalseEnd Sub

Deze code controleert de gehele bladen en verwijdert alle filters die zijn toegepast.

Als u geen filters op het hele blad wilt uitschakelen, maar alleen op een specifieke dataset, gebruikt u de onderstaande code:

Sub TurnOFFAutoFilter() If Worksheets("Sheet1").Range("A1").AutoFilter Then Worksheets("Sheet1").Range("A1").AutoFilter End IfEnd Sub

De bovenstaande code controleert of er al filters zijn toegepast of niet.

Als er al filters zijn toegepast, worden deze verwijderd, anders wordt er niets gedaan.

Ook als u AutoFilter wilt inschakelen, gebruikt u de onderstaande code:

Sub TurnOnAutoFilter() If Not Worksheets("Sheet1").Range("A4").AutoFilter Then Worksheets("Sheet1").Range("A4").AutoFilter End IfEnd Sub

Controleer of AutoFilter al is toegepast

Als u een blad hebt met meerdere datasets en u wilt zeker weten dat er nog geen filters zijn toegepast, kunt u de onderstaande code gebruiken.

Sub CheckforFilters()If ActiveSheet.AutoFilterMode = True ThenMsgBox "There are Filters already in place"ElseMsgBox "There are no filters"End IfEnd Sub

Deze code maakt gebruik van een berichtenboxfunctie die het bericht ‘Er zijn al filters aanwezig’ weergeeft als er filters op het blad worden gevonden, en anders het bericht ‘Er zijn geen filters aanwezig’.

Alle gegevens weergeven

Als u filters hebt toegepast op de gegevensset en u alle gegevens wilt weergeven, gebruikt u de volgende code:

Sub ShowAllData()If ActiveSheet.FilterMode Then ActiveSheet.ShowAllDataEnd Sub

De bovenstaande code controleert of de FilterMode WAAR of ONWAAR is.

Als het waar is, betekent dit dat er een filter is toegepast en dat de methode ShowAllData wordt gebruikt om alle gegevens te tonen.

Merk op dat dit de filters niet verwijdert. De filterpictogrammen kunnen nog steeds worden gebruikt.

Autofilter gebruiken op beveiligde bladen

Als u een blad beveiligt, werken de filters standaard niet.

In het geval dat u al filters hebt, kunt u Autofilter inschakelen om ervoor te zorgen dat het ook werkt op beveiligde bladen.

Om dit te doen, vinkt u de optie Autofilter gebruiken aan terwijl u het blad beveiligt.

Weliswaar werkt dit wanneer u al filters hebt geïnstalleerd, maar wanneer u Autofilters probeert toe te voegen met behulp van een VBA-code, zal dit niet werken.

Omdat het werkblad is beveiligd, kan geen enkele macro worden uitgevoerd om wijzigingen in het Autofilter aan te brengen.

Dus moet u een code gebruiken om het werkblad te beschermen en ervoor te zorgen dat autofilters daarin zijn ingeschakeld.

Dit kan nuttig zijn wanneer u een dynamisch filter hebt gemaakt (iets wat ik heb behandeld in het voorbeeld – ‘Gegevens filteren op basis van een celwaarde’).

Hieronder staat de code die het werkblad zal beschermen, maar u tegelijkertijd in staat zal stellen er zowel filters als VBA-macro’s in te gebruiken.

Private Sub Workbook_Open()With Worksheets("Sheet1").EnableAutoFilter = True.Protect Password:="password", Contents:=True, UserInterfaceOnly:=TrueEnd WithEnd Sub

Deze code moet in het codevenster ThisWorkbook worden geplaatst.

Hier volgen de stappen om de code in het codevenster ThisWorkbook te plaatsen:

1. Open de VB-editor (sneltoets – ALT + F11).
2. In het deelvenster Project Explorer, dubbelklikt u op het object ThisWorkbook.
3. In het codevenster dat wordt geopend, kopieert en plakt u de bovenstaande code.

Zodra u de werkmap opent en macro’s inschakelt, wordt de macro automatisch uitgevoerd en wordt Blad1 beveiligd.

Hoewel, alvorens dat te doen, zal het ‘EnableAutoFilter = True’ specificeren, wat betekent dat de filters ook in het beschermde blad zouden werken.

Ook stelt het het ‘UserInterfaceOnly’ argument op ‘True’ in. Dit betekent dat terwijl het werkblad is beschermd, de VBA macro’s code zou blijven werken.

Je kunt ook graag de volgende VBA Tutorials:

• Excel VBA Loops.
• Cellen filteren met vetgedrukte opmaak.
• Een macro opnemen.
• Gegevens sorteren met VBA.
• Werkbladtabbladen sorteren in Excel.