Excel VBA Autofilter: een complete gids met voorbeelden

Veel Excel-functionaliteiten zijn ook beschikbaar voor gebruik in VBA - en de Autofilter methode is zo'n functionaliteit.

Als u een dataset heeft en deze wilt filteren met een criterium, dan kunt u dit eenvoudig doen met de optie Filter in het Data-lint.

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

Waarom dan zelfs de AutoFilter in VBA gebruiken?

Als u alleen gegevens wilt filteren en wat basisdingen wilt doen, raad ik u aan vast te houden aan de ingebouwde filterfunctionaliteit die de Excel-interface biedt.

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

Stel dat u de gegevens snel wilt filteren op basis van een vervolgkeuzelijst 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 en plakken, kan het veel tijd kosten om dit handmatig te doen.

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

Opmerking: Ik zal dit voorbeeld (over het filteren van gegevens op basis van een vervolgkeuzelijst en kopiëren naar een nieuw blad) later in deze zelfstudie behandelen.

Excel VBA Autofilter-syntaxis

Uitdrukking. AutoFilter( _Veld_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )

• Uitdrukking: Dit is het bereik waarop u het autofilter wilt toepassen.
• Veld: [Optioneel argument] Dit is het kolomnummer dat u wilt filteren. Dit wordt vanaf links geteld in de dataset. Dus als u gegevens wilt filteren op basis van de tweede kolom, is deze waarde 2.
• Criteria1: [Optioneel argument] Dit zijn de criteria waarop u de dataset wilt filteren.
• Operator: [Optioneel argument] Als u ook criteria 2 gebruikt, kunt u deze twee criteria combineren op basis van de Operator. De volgende operators zijn beschikbaar voor gebruik: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamisch, xlFilterFontColor, xlFilterIcon, xlFilterWaarden
• Criteria2: [Optioneel argument] Dit is het tweede criterium waarop u de dataset kunt filteren.
• ZichtbaarDropDown: [Optioneel argument] U kunt aangeven of u het filtervervolgkeuzepictogram in de gefilterde kolommen wilt weergeven of niet. Dit argument kan WAAR of ONWAAR zijn.

Behalve Expressie zijn alle andere argumenten optioneel.

Als u geen enkel argument gebruikt, zou het gewoon de filterpictogrammen op de kolommen toepassen of verwijderen.

Sub FilterRows()-werkbladen ("Filtergegevens").Bereik ("A1").AutoFilter End Sub

De bovenstaande code past eenvoudig de Autofilter-methode toe op de kolommen (of als deze al is toegepast, wordt deze verwijderd).

Dit betekent simpelweg dat als u de filterpictogrammen in de kolomkoppen niet kunt zien, u deze wel zult zien wanneer deze bovenstaande code wordt uitgevoerd, en als u deze kunt zien, wordt deze verwijderd.

Als u gefilterde gegevens heeft, worden de filters verwijderd en wordt de volledige gegevensset weergegeven.

Laten we nu enkele voorbeelden bekijken van het gebruik van Excel VBA Autofilter die het gebruik duidelijk maken.

Voorbeeld: gegevens filteren op basis van een tekstvoorwaarde

Stel, u heeft een dataset zoals hieronder weergegeven en u wilt deze filteren op basis van de kolom ‘Item’.

De onderstaande code zou alle rijen filteren waar het item 'Printer' is.

Sub FilterRows() Worksheets("Blad1").Bereik("A1").AutoFilter Veld:=2, Criteria1:="Printer" End Sub

De bovenstaande code verwijst naar Blad1 en daarin verwijst het naar A1 (wat een cel in de dataset is).

Merk op dat we hier Field:=2 hebben gebruikt, aangezien de itemkolom de tweede kolom in onze dataset van links is.

Als je nu denkt - waarom moet ik dit doen met een VBA-code. Dit kan eenvoudig worden gedaan met behulp van ingebouwde filterfunctionaliteit.

Je hebt gelijk!

Als dit alles is wat u wilt doen, kunt u beter de ingebouwde filterfunctionaliteit gebruiken.

Maar terwijl je de resterende tutorial leest, zul je zien dat dit kan worden gecombineerd met wat extra code om krachtige automatisering te creëren.

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

Klik hier om het voorbeeldbestand te downloaden en te volgen.

Voorbeeld: meerdere criteria (EN/OF) 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("Blad1").Bereik("A1").AutoFilter Veld:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector" End Sub

Merk op dat ik hier de . heb gebruikt xlOR exploitant.

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

Op dezelfde manier kunt u ook de EN-criteria 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("Blad1").Range("A1").AutoFilter Veld:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20" End Sub

Voorbeeld: meerdere criteria met verschillende kolommen

Stel dat u de volgende dataset heeft.

Met Autofilter kunt u meerdere kolommen tegelijk filteren.

Als u bijvoorbeeld alle records wilt filteren waarbij het item 'Printer' en de vertegenwoordiger 'Mark' is, kunt u de onderstaande code gebruiken:

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

Voorbeeld: Top 10-records filteren met de AutoFilter-methode

Stel dat u de onderstaande dataset heeft.

Hieronder vindt u de code die u de top 10 records geeft (op basis van de hoeveelheidskolom):

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

In de bovenstaande code heb ik ActiveSheet gebruikt. U kunt desgewenst de bladnaam gebruiken.

Merk op dat in dit voorbeeld, als je de top 5 items wilt krijgen, je het nummer gewoon in . verandert Criteria1:=”10″ van 10 tot 5.

Dus voor de top 5 items zou de code zijn:

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

Het ziet er misschien raar uit, maar hoeveel topitems je ook wilt, de Operator-waarde blijft altijd xlTop10Artikelen.

Evenzo zou de onderstaande code u de onderste 10 items geven:

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

En als je de onderste 5 items wilt, verander het nummer dan in Criteria1:=”10″ van 10 tot 5.

Voorbeeld: Top 10 procent filteren met behulp van de AutoFilter-methode

Stel dat u dezelfde dataset heeft (zoals gebruikt in de vorige voorbeelden).

Hieronder vindt u de code die u de top 10 procent records geeft (op basis van de hoeveelheidskolom):

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

Omdat we in onze dataset 20 records hebben, worden de top 2 records geretourneerd (wat 10% van het totale aantal records is).

Voorbeeld: Jokertekens gebruiken in Autofilter

Stel dat u een dataset heeft zoals hieronder weergegeven:

Als u alle rijen wilt filteren waar 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 * (sterretje) gebruikt voor en na het woord 'Board' (wat de criteria zijn).

Een asterisk kan een willekeurig aantal tekens vertegenwoordigen. Dus dit zou elk item filteren dat het woord 'bord' bevat.

Voorbeeld: Kopieer gefilterde rijen naar een nieuw blad

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

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

Sub CopyFilteredRows() Dim rng As Range Dim ws As Worksheet If Worksheets("Sheet1").AutoFilterMode = False Then MsgBox "Er zijn geen gefilterde rijen" Exit Sub End If Set rng = Worksheets("Sheet1").AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range("A1") End Sub

De bovenstaande code zou controleren of er gefilterde rijen in Blad1 zijn of niet.

Als er geen gefilterde rijen zijn, wordt een berichtvenster weergegeven waarin dat wordt vermeld.

En als er gefilterde rijen zijn, zal het deze kopiëren, een nieuw werkblad invoegen en deze rijen op dat nieuw ingevoegde werkblad plakken.

Voorbeeld: gegevens filteren op basis van een celwaarde

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

Iets zoals hieronder weergegeven:

Klik hier om het voorbeeldbestand te downloaden en te volgen.

Dit type constructie kan handig zijn als u snel gegevens wilt filteren en deze vervolgens verder in uw werk wilt gebruiken.

Hieronder is de code die dit zal doen:

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

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 een Als Dan Anders-voorwaarde gebruikt om te controleren of de gebruiker 'Alle' heeft geselecteerd in de vervolgkeuzelijst. Als Alles is geselecteerd, wordt de volledige gegevensset weergegeven.

Deze code wordt NIET in een module geplaatst.

In plaats daarvan moet het in de backend van het werkblad met deze gegevens worden geplaatst.

Hier zijn de stappen om deze code in het werkbladcodevenster te plaatsen:

1. Open de VB Editor (sneltoets - ALT + F11).
2. Dubbelklik in het deelvenster Projectverkenner op de naam van het werkblad waarin u deze filterfunctionaliteit wilt hebben.
3. Kopieer en plak de bovenstaande code in het werkbladcodevenster.
4. Sluit de VB-editor.

Wanneer 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 een Als Dan Anders-voorwaarde gebruikt om te controleren of de gebruiker 'Alle' heeft geselecteerd in de vervolgkeuzelijst. Als Alles is geselecteerd, wordt de volledige gegevensset weergegeven.

Zet Excel AutoFilter AAN/UIT met VBA

Wanneer u Autofilter toepast op een celbereik, zijn er mogelijk al enkele filters aanwezig.

U kunt de onderstaande code gebruiken om vooraf toegepaste automatische filters uit te schakelen:

Sub TurnOFFAutoFilter() Worksheets("Blad1").AutoFilterMode = False End Sub

Deze code controleert de volledige bladen en verwijdert eventuele filters die zijn toegepast.

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

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

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

Als er al filters zijn toegepast, verwijdert het deze, anders doet het niets.

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

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

Controleer of AutoFilter al is toegepast

Als je een blad hebt met meerdere datasets en je wilt zeker weten dat er nog geen filters aanwezig zijn, kun je onderstaande code gebruiken.

Sub CheckforFilters() If ActiveSheet.AutoFilterMode = True Dan MsgBox "Er zijn al filters aanwezig" Else MsgBox "Er zijn geen filters" End If End Sub

Deze code gebruikt een berichtvakfunctie die een bericht 'Er zijn al filters aanwezig' weergeeft wanneer er filters op het blad worden gevonden, anders wordt 'Er zijn geen filters' weergegeven.

Toon alle gegevens

Als je filters hebt toegepast op de dataset en je wilt alle data tonen, gebruik dan de onderstaande code:

Sub ShowAllData() Als ActiveSheet.FilterMode Dan ActiveSheet.ShowAllData End Sub

De bovenstaande code controleert of de FilterMode TRUE of FALSE is.

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

Houd er rekening mee dat hiermee de filters niet worden verwijderd. De filterpictogrammen zijn nog steeds beschikbaar voor gebruik.

AutoFilter gebruiken op beveiligde bladen

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

Als u al filters heeft, kunt u AutoFilter inschakelen om ervoor te zorgen dat het zelfs op beveiligde bladen werkt.

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

Hoewel dit werkt als je al filters hebt geïnstalleerd, zal het niet werken als je probeert om Autofilters toe te voegen met behulp van een VBA-code.

Omdat het blad is beveiligd, kan geen enkele macro worden uitgevoerd en kunnen er geen wijzigingen worden aangebracht in het autofilter.

U moet dus een code gebruiken om het werkblad te beschermen en ervoor te zorgen dat automatische filters daarin zijn ingeschakeld.

Dit kan handig zijn wanneer u een dynamisch filter hebt gemaakt (iets dat ik in het voorbeeld heb behandeld - 'Gegevens filteren op basis van een celwaarde').

Hieronder vindt u de code die het blad beschermt, maar waarmee u tegelijkertijd zowel filters als VBA-macro's erin kunt gebruiken.

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

Deze code moet in het codevenster van ThisWorkbook worden geplaatst.

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

1. Open de VB Editor (sneltoets - ALT + F11).
2. Dubbelklik in het deelvenster Projectverkenner op het object ThisWorkbook.
3. Kopieer en plak de bovenstaande code in het codevenster dat wordt geopend.

Zodra u de werkmap opent en macro's inschakelt, wordt de macro automatisch uitgevoerd en wordt Blad1 beschermd.

Voordat u dat doet, wordt echter 'EnableAutoFilter = True' gespecificeerd, wat betekent dat de filters ook in het beveiligde blad zouden werken.

Het stelt ook het argument 'UserInterfaceOnly' in op 'True'. Dit betekent dat terwijl het werkblad is beveiligd, de VBA-macrocode zou blijven werken.

Misschien vind je de volgende VBA-zelfstudies misschien ook leuk:

• Excel VBA-lussen.
• Filtercellen met vetgedrukte lettertype-opmaak.
• Een macro opnemen.
• Gegevens sorteren met VBA.
• Sorteer werkbladtabbladen in Excel.