De nombreuses fonctionnalités Excel sont également disponibles pour être utilisées en VBA - et le Filtre automatique method est l'une de ces fonctionnalités.
Si vous disposez d'un jeu de données et que vous souhaitez le filtrer à l'aide d'un critère, vous pouvez facilement le faire à l'aide de l'option Filtrer dans le ruban Données.
Et si vous en voulez une version plus avancée, il existe également un filtre avancé dans Excel.
Alors pourquoi même utiliser le filtre automatique dans VBA ?
Si vous avez juste besoin de filtrer les données et de faire des choses de base, je vous recommande de vous en tenir à la fonctionnalité de filtre intégrée offerte par l'interface Excel.
Vous devez utiliser VBA Autofilter lorsque vous souhaitez filtrer les données dans le cadre de votre automatisation (ou si cela vous permet de gagner du temps en accélérer le filtrage des données).
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 quelques copier-coller, cela peut vous prendre beaucoup de temps pour le faire manuellement.
Dans un tel scénario, l'utilisation de VBA Autofilter peut accélérer les choses et gagner du temps.
Noter: 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 didacticiel.
Syntaxe du filtre automatique Excel VBA
Expression. Filtre automatique( _Champ_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )
- Expression: Il s'agit de la plage sur laquelle vous souhaitez appliquer le filtre automatique.
- Domaine: [Argument facultatif] Il s'agit du numéro de colonne que vous souhaitez filtrer. Ceci est compté à partir de la gauche dans l'ensemble de données. Donc, si vous souhaitez filtrer les données en fonction de la deuxième colonne, cette valeur serait 2.
- Critères1: [Argument facultatif] Il s'agit des critères en fonction desquels vous souhaitez filtrer l'ensemble de données.
- Opérateur: [Argument facultatif] Si vous utilisez également le critère 2, vous pouvez combiner ces deux critères en fonction de l'opérateur. Les opérateurs suivants peuvent être utilisés : xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
- Critères2: [Argument facultatif] Il s'agit du deuxième critère sur lequel vous pouvez filtrer le jeu de données.
- VisibleDropDown: [Argument facultatif] Vous pouvez spécifier si vous souhaitez que l'icône de la liste déroulante du filtre apparaisse ou non dans les colonnes filtrées. Cet argument peut être VRAI ou FAUX.
À part Expression, tous les autres arguments sont facultatifs.
Si vous n'utilisez aucun argument, cela appliquerait ou supprimerait simplement les icônes de filtre dans les colonnes.
Sub FilterRows() Worksheets("Filtrer les données").Range("A1").AutoFilter End Sub
Le code ci-dessus appliquerait simplement la méthode Autofilter aux colonnes (ou si elle est déjà appliquée, elle 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 à le voir lorsque ce code ci-dessus sera exécuté, et si vous pouvez le voir, il sera supprimé.
Si vous avez des données filtrées, cela supprimera les filtres et vous montrera l'ensemble de données complet.
Voyons maintenant quelques exemples d'utilisation d'Excel VBA Autofilter qui rendront son utilisation claire.
Exemple : Filtrage de données en fonction d'une condition de texte
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez le filtrer en fonction de la colonne « Item ».
Le code ci-dessous filtrerait toutes les lignes où l'élément est « Imprimante ».
Sub FilterRows() Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer" End Sub
Le code ci-dessus fait référence à Sheet1 et à l'intérieur, il fait référence à A1 (qui est une cellule de l'ensemble de données).
Notez qu'ici, nous avons utilisé Field:=2, car la colonne de l'élément est la deuxième colonne de notre ensemble de données à partir de la gauche.
Maintenant, si vous pensez - pourquoi dois-je le faire en utilisant un code VBA. Cela peut facilement être fait en utilisant la fonctionnalité de filtre intégrée.
Vous avez raison!
Si c'est tout ce que vous voulez faire, mieux vaut utiliser la fonctionnalité de filtre intégrée.
Mais en lisant le didacticiel restant, vous verrez que cela peut être combiné avec du code supplémentaire pour créer une automatisation puissante.
Mais avant de vous les montrer, laissez-moi d'abord couvrir quelques exemples pour vous montrer ce que toute la méthode AutoFilter peut faire.
Cliquez ici pour télécharger le fichier d'exemple et suivre.
Exemple : plusieurs critères (ET/OU) dans la même colonne
Supposons que j'ai le même ensemble de données et que cette fois je souhaite filtrer tous les enregistrements où l'élément est soit « Imprimante » ou « Projecteur ».
Le code ci-dessous ferait ceci:
Sub FilterRowsOR() Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector" End Sub
Notez qu'ici j'ai utilisé le xlOR opérateur.
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 les critères ET.
Par exemple, si vous souhaitez filtrer tous les enregistrements dont 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 : plusieurs critères avec différentes colonnes
Supposons que vous ayez l'ensemble de données suivant.
Avec Autofilter, vous pouvez filtrer plusieurs colonnes en même temps.
Par exemple, si vous souhaitez filtrer tous les enregistrements où l'article est « Imprimante » et le représentant des ventes est « Marque », 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 With End Sub
Exemple : filtrer les 10 premiers enregistrements à l'aide de la méthode de filtrage automatique
Supposons que vous ayez l'ensemble de données ci-dessous.
Vous trouverez ci-dessous le code qui vous donnera les 10 premiers enregistrements (en fonction de la colonne quantité) :
Sub FilterRowsTop10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Items End Sub
Dans le code ci-dessus, j'ai utilisé ActiveSheet. Vous pouvez utiliser le nom de la feuille si vous le souhaitez.
Notez que dans cet exemple, si vous souhaitez obtenir les 5 premiers éléments, modifiez simplement le nombre dans Critères1 : = » 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:=xlTop10Items End Sub
Cela peut sembler étrange, mais quel que soit le nombre d'éléments principaux que vous souhaitez, la valeur de l'opérateur reste toujours xlTop10Items.
De même, le code ci-dessous vous donnerait les 10 derniers éléments :
Sub FilterRowsBottom10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10Items End Sub
Et si vous voulez les 5 derniers éléments, changez le nombre dans Critères1 : = » 10″ de 10 à 5.
Exemple : filtrer les 10 % supérieurs à l'aide de la méthode de filtrage automatique
Supposons que vous ayez le même ensemble de données (comme utilisé dans les exemples précédents).
Vous trouverez ci-dessous le code qui vous donnera les 10 % meilleurs enregistrements (en fonction de la colonne de quantité) :
Sub FilterRowsTop10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Percent End Sub
Dans notre ensemble de données, puisque nous avons 20 enregistrements, il renverra les 2 premiers enregistrements (ce qui représente 10 % du total des enregistrements).
Exemple : Utilisation de caractères génériques dans le filtre automatique
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Si vous souhaitez 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. Cela filtrerait donc tout élément contenant le mot « tableau ».
Exemple : copier des lignes filtrées dans une nouvelle feuille
Si vous souhaitez non seulement filtrer les enregistrements en fonction de critères, mais également copier les lignes filtrées, vous pouvez utiliser la macro ci-dessous.
Il 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 Range Dim ws As Worksheet If Worksheets("Sheet1").AutoFilterMode = False Then MsgBox "Il n'y a pas de lignes filtrées" Exit Sub End If Set rng = Worksheets("Sheet1").AutoFilter.Range Set ws = Worksheets.Add rng.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 indiquant cela.
Et s'il y a des lignes filtrées, il les copiera, insèrera une nouvelle feuille de calcul et collera ces lignes sur cette feuille de calcul nouvellement insérée.
Exemple : Filtrer des données en fonction d'une valeur de cellule
En utilisant Autofilter dans 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 souhaitez filtrer rapidement des données, puis les utiliser davantage dans votre travail.
Ci-dessous le code qui fera cela :
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, Critère1:=Plage("B2") Fin si fin si fin sous
Il s'agit d'un code d'événement de feuille de calcul, qui n'est exécuté que 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).
En outre, une condition If Then Else est utilisée pour vérifier si l'utilisateur a sélectionné « Tous » dans la liste déroulante. Si Tout est sélectionné, l'ensemble de données complet est affiché.
Ce code n'est PAS placé dans un module.
Au lieu de cela, il doit être placé dans le backend de la feuille de calcul contenant ces données.
Voici les étapes pour mettre ce code dans la fenêtre de code de la feuille de calcul :
- Ouvrez l'éditeur VB (raccourci clavier - ALT + F11).
- Dans le volet Explorateur de projets, double-cliquez sur le nom de la feuille de calcul dans laquelle vous souhaitez cette fonctionnalité de filtrage.
- Dans la fenêtre de code de la feuille de calcul, copiez et collez le code ci-dessus.
- Fermez l'éditeur VB.
Désormais, lorsque vous utilisez la liste déroulante, elle filtre automatiquement les données.
Il s'agit d'un code d'événement de feuille de calcul, qui n'est exécuté que 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).
En outre, une condition If Then Else est utilisée pour vérifier si l'utilisateur a sélectionné « Tous » dans la liste déroulante. Si Tout est sélectionné, l'ensemble de données complet est affiché.
Activer/désactiver le filtre automatique Excel à l'aide de VBA
Lors de l'application d'Autofilter à une plage de cellules, certains filtres peuvent déjà être en place.
Vous pouvez utiliser le code ci-dessous pour désactiver tous les filtres automatiques pré-appliqués :
Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = False End Sub
Ce code vérifie les feuilles entières et supprime tous les filtres qui ont été appliqués.
Si vous ne souhaitez pas désactiver les filtres de la feuille entière mais uniquement 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 If End Sub
Le code ci-dessus vérifie si des filtres sont déjà 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 souhaitez activer le filtre automatique, utilisez le code ci-dessous :
Sub TurnOnAutoFilter() If Not Worksheets("Sheet1").Range("A4").AutoFilter Then Worksheets("Sheet1").Range("A4").AutoFilter End If End Sub
Vérifiez si le filtre automatique est déjà appliqué
Si vous avez une feuille avec plusieurs jeux de données et que vous voulez vous assurer qu'aucun filtre n'est déjà en place, vous pouvez utiliser le code ci-dessous.
Sub CheckforFilters() If ActiveSheet.AutoFilterMode = True Then MsgBox "Des filtres sont déjà en place" Else MsgBox "Il n'y a pas de filtres" End If End Sub
Ce code utilise une fonction de boîte de message qui affiche un message « Des filtres sont 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 appliqué des filtres à l'ensemble de données et que vous souhaitez afficher toutes les données, utilisez le code ci-dessous :
Sub ShowAllData() Si ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End Sub
Le code ci-dessus vérifie si le FilterMode est TRUE ou FALSE.
Si c'est vrai, cela signifie qu'un filtre a été appliqué et qu'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 du filtre automatique sur les feuilles protégées
Par défaut, lorsque vous protégez une feuille, les filtres ne fonctionneront pas.
Si vous avez déjà des filtres en place, vous pouvez activer le filtre automatique pour vous assurer qu'il fonctionne même sur des feuilles protégées.
Pour cela, cochez l'option Utiliser le filtre automatique tout en protégeant la feuille.
Bien que cela fonctionne lorsque vous avez déjà des filtres en place, si vous essayez d'ajouter des filtres automatiques à l'aide d'un code VBA, cela ne fonctionnera pas.
Étant donné que la feuille est protégée, elle ne permettrait à aucune macro de s'exécuter et d'apporter des modifications au filtre automatique.
Vous devez donc utiliser un code pour protéger la feuille de calcul et vous assurer que les filtres automatiques y sont activés.
Cela peut être utile lorsque vous avez créé un filtre dynamique (quelque chose que j'ai couvert dans l'exemple - « Filtrer les données en fonction d'une valeur de cellule »).
Vous trouverez ci-dessous le code qui protégera la feuille, mais vous permettra en même temps d'utiliser des filtres ainsi que des macros VBA.
Private Sub Workbook_Open() With Worksheets("Sheet1") .EnableAutoFilter = True .Protect Password:="password", Contents:=True, UserInterfaceOnly:=True End With End Sub
Ce code doit être placé dans la fenêtre de code ThisWorkbook.
Voici les étapes pour mettre le code dans la fenêtre de code ThisWorkbook :
- Ouvrez l'éditeur VB (raccourci clavier - ALT + F11).
- Dans le volet Explorateur de projets, double-cliquez sur l'objet ThisWorkbook.
- Dans la fenêtre de code qui s'ouvre, copiez et collez le code ci-dessus.
Dès que vous ouvrez le classeur et activez les macros, il exécutera la macro automatiquement et protégera Sheet1.
Cependant, avant de faire cela, il spécifiera « EnableAutoFilter = True », ce qui signifie que les filtres fonctionneraient également dans la feuille protégée.
En outre, il définit l'argument « UserInterfaceOnly » sur « True ». Cela signifie que tant que la feuille de calcul est protégée, le code des macros VBA continue de fonctionner.
Vous aimerez peut-être aussi les didacticiels VBA suivants :
- Boucles Excel VBA.
- Filtrez les cellules avec une mise en forme de police en gras.
- Enregistrement d'une macro.
- Trier les données à l'aide de VBA.
- Trier les onglets de feuille de calcul dans Excel.
