Filtrer un tableau Excel avec plusieurs critères en VBA

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Filtrer un tableau sur de nombreux critères

Grâce à la méthode VBA Find, nous avons déjà appris à isoler des données précises dans un tableau Excel. Ici, nous souhaitons l'engager dans une boucle de manière à considérer un nombre variable de conditions, pour filtrer automatiquement les lignes d'une base de données sur demande.

Classeur Excel à télécharger
Pour la démonstration de cette nouvelle astuce, nous souhaitons appuyer les manipulations sur un classeur Excel hébergeant une base de données et livrant quelques petits travaux déjà amorcés.

• Télécharger le classeur masquer-sur-critere.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons une base de données des activités de sorties. Elle est composée de plusieurs centaines de lignes. Sur la droite de ce tableau, une colonne permet d'émettre les départements à exclure du visuel, grâce à des listes de choix. Juste au-dessus, deux boutons se proposent. Le premier permet de réafficher toutes les lignes, si d'aventure certaines avaient été masquées par une précédente action. Le bouton du dessous permet de lancer le traitement VBA pour filtrer les lignes de la base de données, en tenant compte de tous les critères énoncés, c'est-à-dire de tous les départements mentionnés à masquer.

Afficher toutes les lignes et colonnes en VBA
Nous allons le découvrir, les deux boutons sont déjà attachés à des procédures de code. De surcroît, le premier est déjà fonctionnel.

• Cliquer droit sur le bouton intitulé Afficher,
• En bas du menu contextuel, choisir la commande Affecter une macro,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Modifier,

Nous basculons ainsi dans l'éditeur VBA Excel entre les bornes de la procédure afficher.

Sub afficher()
Rows.Hidden = False
Columns.Hidden = False
End Sub

Son code se déclenchera sur le bouton du même nom. Nous y exploitons les collections Rows et Columns même si dans notre cas, la seconde n'est pas utile. Elles représentent respectivement les collections des lignes et des colonnes de la feuille active. Avec la propriété Hidden réglée à False, nous les réaffichons toutes.

Juste en-dessous, vous notez la présence de la procédure filtrer. Elle est vide pour l'instant. Elle est associée au second bouton.

Sub filtrer()

End Sub

C'est elle que nous devons implémenter pour filtrer les lignes du tableau en prenant en considération les départements émis par l'utilisateur, dans les cellules du dessous, à partir de la ligne 4.

Les variables
Et justement pour commencer, nous devons d'abord déclarer les variables utiles.

• Dans les bornes de la procédure filtrer, ajouter les instructions VBA suivantes :

...
Dim critere As String: Dim ligne As Byte
Dim plage As Range

ligne = 4
Application.ScreenUpdating = False
...

La variable critere déclarée comme un texte (As String) devra représenter tour à tour tous les départements émis en conditions de filtres dans la colonne H. La variable ligne sera exploitée dans une boucle pour incrémenter son compteur et ainsi passer naturellement sur le prochain critère à utiliser pour filtrer les lignes du tableau. Ensuite, la variable plage déclarée comme un Range (Plage de cellules) servira à exercer la recherche (Find) dans la colonne des départements (D). Ensuite, nous initialisons la variable ligne sur la position du premier critère (4) potentiellement émis. Puis, nous réglons à False la propriété ScreenUpdating de l'objet Application pour empêcher le rafraîchissement de l'écran pendant le traitement.

Parcourir tous les critères
Pour que tous les départements des lignes à masquer soient pris en compte, nous devons parcourir toutes les lignes de la colonne H (8), à partir de la cellule H4. Tant qu'une cellule non vide est détectée, nous devons exploiter l'information avec la méthode Find pour cacher tous les enregistrements correspondants dans la base de données.

• A la suite du code VBA, créer la boucle While comme suit :

...
While Cells(ligne, 8).Value <> ""
critere = Cells(ligne, 8).Value

ligne = ligne + 1
Wend
...

Tant que la cellule des critères n'est pas vide (Cells(ligne, 8).Value <>""), nous prélevons le département pour la ligne en cours dans la variable critere. Puis, nous incrémentons la variable ligne pour analyser le potentiel département suivant à filtrer.

Filtrer les lignes du tableau
Entre temps bien sûr, donc avant l'incrémentation, nous devons exercer la recherche du critère en cours pour masquer les lignes qui le concernent.

• A la suite du code dans la boucle, créer l'instruction conditionnelle suivante :

...
critere = Cells(ligne, 8).Value
Set plage = Columns("D:D").Find(critere, LookIn:=xlValues)
If Not plage Is Nothing Then
plage.EntireRow.Hidden = True
End If
ligne = ligne + 1
...

Nous initialisons notre objet plage en exerçant la recherche du critère (Find) sur la colonne D, celle des départements. Comme cette initialisation est réalisée dans une boucle qui parcourt tous les critères, ils seront tous étudiés tour à tour. Ensuite, nous testons le résultat de la recherche par une double négation (Not ... Is Nothing). En d'autres termes, si la plage répond bien par une valeur concordant avec le critère en cours, nous masquons la ligne entière grâce à la propriété enfant Hidden de la propriété EntireRow (Ligne entière) de l'objet plage.

A ce stade, nous ne masquons que la première ligne portant le critère (département) en cours d'étude. Pour que toutes les lignes concernées soient masquées, nous devons poursuivre la recherche grâce à la méthode FindNext engagée dans une boucle.

• A la suite dans l'instruction conditionnelle, imbriquer la nouvelle boucle suivante :

...
If Not plage Is Nothing Then
plage.EntireRow.Hidden = True

Do
Set plage = Columns("D:D").FindNext(plage)
If Not plage Is Nothing Then
plage.EntireRow.Hidden = True
Else
Exit Do
End If
Loop
End If
...

Dans la boucle et grâce à la méthode FindNext, nous entreprenons la recherche de tous les résultats suivants pour le critère en cours. Dès qu'une ligne concorde, comme précédemment, nous la masquons.

Il ne nous reste plus qu'à décharger l'objet de la mémoire et à autoriser de nouveau l'actualisation de rafraîchissement de l'écran, pour apprécier les résultats filtrés.

• Après la boucle While, ajouter les deux instructions VBA suivantes :

...
Wend

Set plage = Nothing
Application.ScreenUpdating = True

End Sub
...

Nous réinitialisons (Set) notre objet à Nothing pour le détruire. Puis, nous recalons la propriété ScreenUpdating sur la valeur True, pour rendre la main à l'affichage actualisé.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + Tab),
• Potentiellement, ajouter ou modifier des départements en colonne H,

Désormais, si vous faites défiler le tableau vers le bas, vous constatez des ruptures dans l'énumération des lignes de la base de données d'origine. Les lignes des départements mentionnés en colonne H ont toutes disparu. Nous avons donc réussi à filtrer un tableau sur de multiples critères dynamiques, grâce au code VBA.