Filteren in een macro

Bijna iedere Excel gebruiker wel weet hoe je in een dataset kunt filteren. Als je dit weet dan is een makkelijke vervolgstap om deze acties op te nemen met een macro recorder. Bij het sorteren hebben we gezien dat de opgenomen code vrij onleesbaar is en veel onnodige elementen bevat. Dat is bij filteren niet het geval. 

Filtercode met behulp van de macro recorder

Stel we hebben product data van een bakker in kolom A & B van je eerste werkblad staan.

Laten we nu met de hand de volgende filter instelling toepassen en dit tegelijkertijd opnemen met de macro recorder. Filter op prijs, zodat je alleen nog de producten ziet met een prijs van 75 cent. Je opgenomen code ziet er dan als het goed is als volgt uit.

Sub Macro1()
'
' Macro1 Macro
'

'
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$8").AutoFilter Field:=2, Criteria1:="0,75"
End Sub

Filtercode zelf geschreven met VBA

Zoals je kunt zien zitten er 2 select regels tussen die je meteen kunt verwijderen. Als je dit gedaan hebt dan ben je al bijna klaar. Als wij de code zelf zouden schrijven dan gebruik je bijna nooit de verwijzing naar de ‘activeSheet’. Het is veel robuuster om specifiek de naam van de sheet mee te geven waar de data zich bevindt. Zo voorkom je het risico dat je script fout loopt mocht de juiste Excel sheet onverwachts niet de actieve sheet zijn. Je schrijft de code dan als volgt.

Sub FilterOefening1()

    ThisWorkbook.Sheets("Blad1").Range("$A$1:$B$8").AutoFilter Field:=2, Criteria1:="0,75"
    
End Sub

Ik ben er in mijn voorbeeld wel vanuit gegaan dat je mijn opdracht hebt uitgevoerd in een nieuw werkblad waarvan de enige tab ‘Blad1’ genoemd is. Mocht de data in een tab staan met een andere naam dan moet je dat natuurlijk aanpassen. Zoals je kunt zien is er maar één regel code nodig voor het opzetten van een filter. Je hoeft alleen maar een range te bepalen gevolgd door de AutoFilter functie. Daarbij heb je in het geval van 1 Criteria maar 1 Field die je dient aan te geven plus de waarde van de Criteria. 

Stel, je wilt een extra criteria wilt toevoegen aan het voorbeeld dat wij hiervoor gebruikt hebben. Naast dat je wilt filteren op producten met een prijs van 75 cent wil je ook dat producten met een prijs  van 50 cent zichtbaar worden. Je voegt dan een ‘Operator’ toe ‘xlOr’ in dezelfde functie gevolgd door de extra criteria. je code wordt dan

ThisWorkbook.Sheets("Blad1").Range("$A$1:$B$8").AutoFilter Field:=2, Criteria1:="1", Operator:=xlOr, Criteria2:="=0,75"

Als deze 2e criteria betrekking had op de eerste kolom uit je data (Field:=1) dan heb je de Operator niet nodig. Stel we filteren naast de prijs ook op de tekst ‘volkoren croissant’ uit de eerste kolom dan schrijf je simpelweg.

ThisWorkbook.Sheets("Blad1").Range("$A$1:$B$8").AutoFilter Field:=1, Criteria1:="volkoren croissant", Field:=2, Criteria1:="0,75"

Mocht je een hele serie aan criteria’s willen instellen als filter dan kun je deze beter onder elkaar plaatsen om de leesbaarheid van je script te verhogen. Je kunt deze regels dan tussen een With statement plaatsen. Dit ziet er als volgt uit.

With ThisWorkbook.Sheets("Blad1").Range("$A$1:$B$8")
    .AutoFilter Field:=1, Criteria1:="volkoren croissant"
    .AutoFilter Field:=2, Criteria1:="0,75"
End With

Welke operators kan ik toepassen bij een filtering?

Naast de operators xlOr en xlAnd zijn er nog een aantal anderen die je kunt gebruiken bij het aanbrengen van een filter. Op deze website van Microsoft staan alle operatoren weergegeven in een tabel met een beschrijving van wat de operator exact doet.

Alle filters in je Excel werkmap verwijderen 

Het kan voorkomen dat input data voor je macro onverwacht gefilterd is. De output van je macro zal dan ook niet zijn wat je verwacht. Om er zeker van te zijn dat data ongefilterd is voordat je deze uitleest met je macro kun je een stuk code toepassen dat alle filtering in een complete werkmap verwijderd. Je kunt deze code ook plaatsen in een functie die je aan het begin van je macro aanroept. Lees hier hoe je deze functie zelf kunt schrijven.

Ik hoop dat je nu helemaal op de hoogte bent omtrent het gebruik van filtering binnen macro’s. Mocht je toch nog een vraag hebben, laat het weten in de reacties!