Excel VBA Autofiltre : Un guide complet avec des exemples

De nombreuses fonctionnalités d’Excel sont également disponibles pour être utilisées en VBA – et la méthode Autofiltre est l’une de ces fonctionnalités.

Si vous avez un ensemble de données et que vous voulez le filtrer en utilisant un critère, vous pouvez facilement le faire en utilisant l’option Filtre dans le ruban Données.

Et si vous voulez une version plus avancée de celui-ci, il existe également un filtre avancé dans Excel.

Alors pourquoi même utiliser l’AutoFiltre en VBA ?

Si vous avez juste besoin de filtrer des données et de faire quelques trucs de base, je vous recommanderais de vous en tenir à la fonctionnalité de filtre intégrée que l’interface Excel offre.

Vous devriez utiliser l’Autofiltre VBA lorsque vous voulez filtrer les données dans le cadre de votre automatisation (ou si cela vous aide à gagner du temps en rendant le filtrage des données plus rapide).

Par exemple, supposons que vous souhaitiez filtrer rapidement les données en fonction d’une sélection déroulante, puis copier ces données filtrées dans une nouvelle feuille de calcul.

Bien que cela puisse être fait en utilisant la fonctionnalité de filtre intégrée avec un certain copier-coller, cela peut vous prendre beaucoup de temps de le faire manuellement.

Dans un tel scénario, l’utilisation de VBA Autofilter peut accélérer les choses et gagner du temps.

Note : Je couvrirai cet exemple (sur le filtrage des données basé sur une sélection déroulante et la copie dans une nouvelle feuille) plus tard dans ce tutoriel.

Syntaxe du filtre automatique Excel VBA

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

• Expression : Il s’agit de la plage sur laquelle vous voulez appliquer le filtre automatique.
• Champ : Il s’agit du numéro de la colonne sur laquelle vous souhaitez appliquer le filtre. Celui-ci est compté à partir de la gauche dans l’ensemble de données. Donc, si vous voulez filtrer les données en fonction de la deuxième colonne, cette valeur sera 2.
• Critère1 : C’est le critère en fonction duquel vous voulez filtrer l’ensemble de données.
• Opérateur : Dans le cas où vous utilisez également le critère 2, vous pouvez combiner ces deux critères sur la base de l’Opérateur. Les opérateurs suivants peuvent être utilisés : xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Critère2 : C’est le deuxième critère sur lequel vous pouvez filtrer l’ensemble de données.
• VisibleDropDown : Vous pouvez spécifier si vous voulez que l’icône déroulante du filtre apparaisse dans les colonnes filtrées ou non. Cet argument peut être VRAI ou FAUX.

À part Expression, tous les autres arguments sont facultatifs.

Dans le cas où vous n’utilisez aucun argument, il appliquerait ou supprimerait simplement les icônes de filtre aux colonnes.

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

Le code ci-dessus appliquerait simplement la méthode Autofilter aux colonnes (ou si elle est déjà appliquée, il la supprimera).

Cela signifie simplement que si vous ne pouvez pas voir les icônes de filtre dans les en-têtes de colonne, vous commencerez à les voir lorsque ce code ci-dessus sera exécuté, et si vous pouvez les voir, alors ils seront supprimés.

Dans le cas où vous avez des données filtrées, il supprimera les filtres et vous montrera l’ensemble de données complet.

Maintenant, voyons quelques exemples d’utilisation d’Excel VBA Autofilter qui rendront son utilisation claire.

Exemple : Filtrer des données en fonction d’une condition de texte

Supposons que vous avez un ensemble de données comme indiqué ci-dessous et que vous voulez le filtrer en fonction de la colonne ‘Item’.

Le code ci-dessous filtrerait toutes les lignes où l’item est ‘Printer’.

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

Le code ci-dessus se réfère à Sheet1 et à l’intérieur de celui-ci, il se réfère à A1 (qui est une cellule dans l’ensemble de données).

Notez qu’ici nous avons utilisé Field:=2, car la colonne item est la deuxième colonne dans notre ensemble de données à partir de la gauche.

Mais avant de vous les montrer, laissez-moi d’abord couvrir quelques exemples pour vous montrer tout ce que la méthode AutoFilter peut faire.

Cliquez ici pour télécharger le fichier d’exemple et suivez-le.

Exemple : Critères multiples (ET/OU) dans la même colonne

Supposons que j’ai le même ensemble de données, et que cette fois je veux filtrer tous les enregistrements où l’élément est soit ‘Imprimante’ soit ‘Projecteur’.

Le code ci-dessous ferait cela:

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

Notez qu’ici j’ai utilisé l’opérateur xlOR.

Cela indique à VBA d’utiliser les deux critères et de filtrer les données si l’un des deux critères est rempli.

De même, vous pouvez également utiliser le critère AND.

Par exemple, si vous voulez filtrer tous les enregistrements où la quantité est supérieure à 10 mais inférieure à 20, vous pouvez utiliser le code ci-dessous:

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

Exemple : Critères multiples avec différentes colonnes

Supposons que vous avez l’ensemble de données suivant.

Avec Autofilter, vous pouvez filtrer plusieurs colonnes en même temps.

Par exemple, si vous voulez filtrer tous les enregistrements où l’article est ‘Imprimante’ et le représentant commercial est ‘Mark’, vous pouvez utiliser le code ci-dessous:

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

Exemple : Filtrer les 10 premiers enregistrements en utilisant la méthode AutoFilter

Supposons que vous ayez le jeu de données ci-dessous.

Voici le code qui vous donnera les 10 premiers enregistrements (basés sur la colonne de quantité):

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

Dans le code ci-dessus, j’ai utilisé ActiveSheet. Vous pouvez utiliser le nom de la feuille si vous voulez.

Notez que dans cet exemple, si vous voulez obtenir les 5 premiers éléments, il suffit de changer le nombre dans Criteria1:= »10″ de 10 à 5.

Donc pour les 5 premiers éléments, le code serait:

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

Cela peut sembler bizarre, mais peu importe le nombre d’éléments supérieurs que vous voulez, la valeur de l’opérateur reste toujours xlTop10Items.

De même, le code ci-dessous vous donnerait les 10 éléments inférieurs :

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

Et si vous voulez les 5 éléments inférieurs, changez le nombre dans Criteria1:= »10″ de 10 à 5.

Exemple : Filtrer les 10 premiers pour cent en utilisant la méthode AutoFilter

Supposons que vous avez le même ensemble de données (que celui utilisé dans les exemples précédents).

Voici le code qui vous donnera les 10 premiers pour cent des enregistrements (basés sur la colonne de quantité):

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

Dans notre ensemble de données, puisque nous avons 20 enregistrements, il retournera les 2 premiers enregistrements (ce qui représente 10% du total des enregistrements).

Exemple : Utilisation des caractères génériques dans le filtre automatique

Supposons que vous avez un ensemble de données comme indiqué ci-dessous :

Si vous voulez filtrer toutes les lignes où le nom de l’élément contient le mot ‘Board’, vous pouvez utiliser le code ci-dessous :

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

Dans le code ci-dessus, j’ai utilisé le caractère générique * (astérisque) avant et après le mot ‘Board’ (qui est le critère).

Un astérisque peut représenter n’importe quel nombre de caractères. Ainsi, cela filtrerait tout élément contenant le mot ‘board’.

Exemple : Copier les lignes filtrées dans une nouvelle feuille

Si vous voulez non seulement filtrer les enregistrements en fonction de critères mais aussi copier les lignes filtrées, vous pouvez utiliser la macro ci-dessous.

Elle copie les lignes filtrées, ajoute une nouvelle feuille de calcul, puis colle ces lignes copiées dans la nouvelle feuille.

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

Le code ci-dessus vérifierait s’il y a des lignes filtrées dans Sheet1 ou non.

S’il n’y a pas de lignes filtrées, il affichera une boîte de message le précisant.

Et s’il y a des lignes filtrées, il copiera celles-ci, insérera une nouvelle feuille de calcul et collera ces lignes sur cette feuille de calcul nouvellement insérée.

Exemple : Filtrer les données en fonction de la valeur d’une cellule

En utilisant Autofiltre en VBA avec une liste déroulante, vous pouvez créer une fonctionnalité où dès que vous sélectionnez un élément dans la liste déroulante, tous les enregistrements de cet élément sont filtrés.

Quelque chose comme indiqué ci-dessous:

Cliquez ici pour télécharger le fichier d’exemple et suivre.

Ce type de construction peut être utile lorsque vous voulez filtrer rapidement des données et les utiliser ensuite dans votre travail.

Voici le code qui va faire cela :

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

C’est un code d’événement de feuille de calcul, qui s’exécute uniquement lorsqu’il y a un changement dans la feuille de calcul et que la cellule cible est B2 (où nous avons la liste déroulante).

De plus, une condition If Then Else est utilisée pour vérifier si l’utilisateur a sélectionné ‘All’ dans la liste déroulante. Si All est sélectionné, l’ensemble des données est affiché.

Ce code n’est PAS placé dans un module.

Au contraire, il doit être placé dans le backend de la feuille de calcul qui a ces données.

Voici les étapes pour placer ce code dans la fenêtre de code de la feuille de calcul :

1. Ouvrir l’éditeur VB (raccourci clavier – ALT + F11).
2. Dans le volet de l’explorateur de projet, double-cliquez sur le nom de la feuille de calcul dans laquelle vous voulez cette fonctionnalité de filtrage.
3. Dans la fenêtre de code de la feuille de travail, copiez et collez le code ci-dessus.
4. Fermez l’éditeur VB.

Maintenant, lorsque vous utilisez la liste déroulante, elle filtrera automatiquement les données.

C’est un code d’événement de feuille de travail, qui s’exécute uniquement lorsqu’il y a un changement dans la feuille de travail et que la cellule cible est B2 (où nous avons la liste déroulante).

De plus, une condition If Then Else est utilisée pour vérifier si l’utilisateur a sélectionné ‘All’ dans la liste déroulante. Si All est sélectionné, l’ensemble des données est affiché.

Activer/désactiver le filtre automatique d’Excel en utilisant VBA

Lorsqu’on applique le filtre automatique à une plage de cellules, il se peut qu’il y ait déjà des filtres en place.

Vous pouvez utiliser le code ci-dessous pour désactiver tout filtre automatique pré-appliqué :

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

Ce code vérifie les feuilles entières et supprime tous les filtres qui ont été appliqués.

Si vous ne voulez pas désactiver les filtres de la feuille entière mais seulement d’un ensemble de données spécifique, utilisez le code ci-dessous:

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

Le code ci-dessus vérifie s’il y a déjà des filtres en place ou non.

Si des filtres sont déjà appliqués, il les supprime, sinon il ne fait rien.

De même, si vous voulez activer l’AutoFiltre, utilisez le code ci-dessous :

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

Vérifier si l’AutoFiltre est déjà appliqué

Si vous avez une feuille avec plusieurs ensembles de données et que vous voulez vous assurer que vous savez qu’il n’y a pas de filtres déjà en place, vous pouvez utiliser le code ci-dessous.

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

Ce code utilise une fonction de boîte à message qui affiche un message ‘Il y a des filtres déjà en place’ lorsqu’il trouve des filtres sur la feuille, sinon il affiche ‘Il n’y a pas de filtres’.

Afficher toutes les données

Si vous avez des filtres appliqués au jeu de données et que vous voulez afficher toutes les données, utilisez le code suivant :

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

Le code ci-dessus vérifie si le FilterMode est VRAI ou FAUX.

Si c’est vrai, cela signifie qu’un filtre a été appliqué et il utilise la méthode ShowAllData pour afficher toutes les données.

Notez que cela ne supprime pas les filtres. Les icônes de filtre sont toujours disponibles pour être utilisées.

Utilisation d’AutoFiltre sur les feuilles protégées

Par défaut, lorsque vous protégez une feuille, les filtres ne fonctionnent pas.

Dans le cas où vous avez déjà des filtres en place, vous pouvez activer AutoFiltre pour vous assurer qu’il fonctionne même sur les feuilles protégées.

Pour ce faire, cochez l’option Utiliser Autofiltre tout en protégeant la feuille.

Bien que cela fonctionne lorsque vous avez déjà des filtres en place, au cas où vous essayez d’ajouter des Autofiltres en utilisant un code VBA, cela ne fonctionnera pas.

Puisque la feuille est protégée, elle ne permettrait à aucune macro de s’exécuter et d’apporter des modifications à l’Autofiltre.

Donc, vous devez utiliser un code pour protéger la feuille de calcul et vous assurer que les filtres automatiques sont activés dans celle-ci.

Cela peut être utile lorsque vous avez créé un filtre dynamique (ce que j’ai couvert dans l’exemple – ‘Filtrer les données en fonction de la valeur d’une cellule’).

Voici le code qui protégera la feuille, mais en même temps, vous permettra d’utiliser des Filtres ainsi que des macros VBA dans celle-ci.

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

Ce code doit être placé dans la fenêtre de code ThisWorkbook.

Voici les étapes pour placer le code dans la fenêtre de code ThisWorkbook :

1. Ouvrir l’éditeur VB (raccourci clavier – ALT + F11).
2. Dans le volet de l’explorateur de projet, double-cliquez sur l’objet ThisWorkbook.
3. Dans la fenêtre de code qui s’ouvre, copiez et collez le code ci-dessus.

Dès que vous ouvrirez le classeur et que vous activerez les macros, il exécutera automatiquement la macro et protégera Sheet1.

Mais avant de le faire, il spécifiera ‘EnableAutoFilter = True’, ce qui signifie que les filtres fonctionneraient également dans la feuille protégée.

De plus, il définit l’argument ‘UserInterfaceOnly’ à ‘True’. Cela signifie que pendant que la feuille de calcul est protégée, le code des macros VBA continuerait à fonctionner.

Vous pourriez également aimer les tutoriels VBA suivants :

• Boucles VBA Excel.
• Filtrer les cellules avec un formatage en caractères gras.
• Enregistrer une macro.
• Trier les données en utilisant VBA.
• Trier les onglets de la feuille de calcul dans Excel.