Comment filtrer des données en VBA sur Excel

Dans ce tutoriel, nous allons voir comment filtrer des données contenues dans une feuille de calcul, en utilisant une macro commande en VBA. Pour cela, nous allons utiliser l’instruction Autofilter.

Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier en suivant le lien disponible ci-dessous :

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

1. Présentation du cas

Nous allons partir d’un cas que vous devriez bien connaître si vous suivez les tutoriels de la chaîne, il s’agit en effet de la base des données des pays des quatre continents, qui nous a déjà servi de support à de nombreuses reprises.

Dans ce fichier, nous retrouvons ainsi la liste de tous les pays, classés par continent, et pour chacun d’entre nous y associons le nom de la capitale.

Vous pouvez télécharger le fichier de travail dans le lien disponible ci-dessus, pour réaliser les manipulations en même temps que moi.

2. Création du bouton et de la macro commande VBA

L’objectif de ce tutoriel va être de d’effectuer un filtre sur ces données en utilisant un bouton qui fera appel à une macro en VBA.

Nous allons donc commencer par créer ce bouton, et la macro correspondante. Il existe plusieurs méthodes pour créer un tel bouton, ici nous allons choisir d’insérer une simple zone de texte (Insertion > Zone de texte) :

Puis, nous personnalisons l’apparence de ce bouton (dans le menu Format du ruban, qui n’apparaît que lorsque le bouton est sélectionné) :

Cela étant fait, nous allons spécifier à Excel que ce bouton doit permettre de lancer une macro commande, en effectuant un clic-droit sur ce dernier, puis Affecter une macro… :

Dans la fenêtre qui apparaît à l’écran, nous donnons un nom à la macro (par exemple « filtrerLesDonnees ») :

Puis nous confirmons, non pas en appuyant sur le bouton [OK], mais sur le bouton [Nouvelle], ce qui va avoir pour effet de créer une nouvelle macro, directement insérer dans un nouveau module, avec le nom que nous lui avons donné :

3. La macro commande VBA

Pour pouvoir filtrer les données contenues dans notre feuille de calcul, nous allons utiliser l’instruction VBA Autofilter, qui permet de réaliser une opération équivalente à ce que permet le bouton de Filtre d’Excel (présent dans le menu Données).

Ici, nous allons utiliser cette instruction Autofilter de la manière suivante :

Expression.AutoFilter( _Field_ , _Criteria1_ )

Ainsi, nous allons avoir besoin de connaître trois éléments :

• Expression : il s’agit d’un objet de type range (donc une ou plusieurs cellules), qui représente ici la première cellule du tableau. Nous verrons juste après comment instancier et définir cet objet,
• _Field_ : il s’agit du numéro de la colonne sur laquelle nous souhaitons appliquer le filtre au sein du tableau contenant les données,
• _Criteria1_ : il s’agit simplement du critère que nous allons appliquer pour mettre en place le filtre

   3.1. Le paramètre « Expression » 

Nous l’avons vu à l’instant, ce paramètre représente la première cellule de la table.

Nous commençons donc par créer une nouvelle variable, de type Range, à laquelle nous allons donner le nom de « premiereCellule »

Sub filtrerLesDonnees()
    ' Détermination de la première cellule du tableau
    Dim premiereCellule As Range
End Sub

Ensuite, nous allons définir quelle cellule de la feuille de calcul doit être insérer dans cette variable.

Pour cela, nous allons partir de la cellule sélectionnée par l’utilisateur juste avant de lancer la macro commande (qui est renvoyée par Excel dans la variable ActiveCell) :

Sub filtrerLesDonnees()
    ' Détermination de la première cellule du tableau
    Dim premiereCellule As Range
    Set premiereCellule = ActiveCell
    …
 

Sauf que nous souhaitons récupérer la première cellule du tableau, et non la cellule active.

Nous allons donc récupérer toute la plage de cellule de la zone active, en utilisant l’instruction CurrentRegion, ensuite, il va nous suffire de n’en extraire que la première cellule :

Sub filtrerLesDonnees()
    ' Détermination de la première cellule du tableau
    Dim premiereCellule As Range
    Set premiereCellule = ActiveCell.CurrentRegion.Cells(1)
    …
 

Attention toutefois à l’utilisation de CurrentRegion, car la région active retournée est déterminée en fonction des données contenues autour de la cellule active. Il est donc important que le tableau des données ne soit entouré d’aucune cellule (en d’autres termes, il faut laisser une ligne vide au-dessus du tableau !)

   3.2. Le paramètre « _Field_ »

Le second paramètre qui va nous permettre de filtrer les données est le paramètre « _Field_ ». Celui-ci correspondant au numéro de la colonne sur laquelle nous souhaitons appliquer le filtre.

Nous supposons ici que l’utilisateur a préalablement cliqué sur l’une des cellules de la colonne sur laquelle le filtre doit être appliqué.

Le numéro de la colonne s’obtient alors par différence entre la colonne sélectionnée, et la première colonne du tableau :

Sub filtrerLesDonnees()
    …
    ' Dénombrement de la colonne à filtrer
    Dim colonne As Integer
    colonne = ActiveCell.Column - premiereCellule.Column + 1
    …
 

Nous ajoutons une colonne, car la première colonne correspond à la colonne « 1 », et non « 0 ».

   3.3. Le paramètre « _Criteria1_ »

Enfin, le dernière paramètre permet de spécifier sur quel critère Excel doit appliquer le filtre. Il s’agit d’une simple chaîne de caractères, que nous allons demander à l’utilisateur de spécifier dans une boîte de dialogue :

Sub filtrerLesDonnees()
    …
    ' Récupération du filtre à effectuer
    Dim filtre As String
    filtre = InputBox("Texte à filtrer :", "Filtre", ActiveCell)
    …
 

Le troisième paramètre de l’instruction InputBox permet de définir un texte par défaut, nous affichons simplement la valeur de la cellule sélectionnée.

   3.4. Et enfin le filtre !

Nous l’avons vu à plusieurs reprises depuis le début de ce tutoriel, nous allons maintenant utiliser l’instruction Autofilter :

Sub filtrerLesDonnees()
    …
    ' Application du filtre
    premiereCellule.AutoFilter field:=colonne, Criteria1:=filtre
    …
 

L’utilisation que nous voyons ici permet de filtrer exactement sur le critère défini par l’utilisateur !

Nous pouvons modifier cette ligne pour définir si la cellule doit contenir le texte, commencer par le texte, finir par le texte, …

Sub filtrerLesDonnees()
    …
    ' Filtre exacte :
    premiereCellule.AutoFilter field:=colonne, Criteria1:=filtre
    …
    ' Filtre terminant par  :
    premiereCellule.AutoFilter field:=colonne, Criteria1:="*" & filtre
    …
    ' Filtre commençant par  :
    premiereCellule.AutoFilter field:=colonne, Criteria1:=filtre & "*"
    …
    ' Filtre contenant  :
    premiereCellule.AutoFilter field:=colonne, Criteria1:="*" & filtre & "*"
    …

Quelques exemples :

•        Le pays fini par « sud » :

•        Le pays commence par « ma » :

•        Le pays contient « fr », à n’importe quel endroit :

De plus, lorsque nous effectuons un second filtre sur une deuxième colonne, nous allons pouvoir constater que ceux-ci se cumulent :

Dans cet exemple, nous avions tout d’abord demandé de filtrer les lignes concernant les pays qui contiennent les deux lettres « fr », puis nous avons effectué un second filtre sur les pays du continent Europe.

Pour modifier ce comportement, il suffit d’ajouter un Autofiltre vide de tout paramètre pour désactiver tous les filtres en cours d’utilisation :

Sub filtrerLesDonnees()
    …
    ' Application du filtre
    premiereCellule.AutoFilter
    premiereCellule.AutoFilter field:=colonne, Criteria1:="*" & filtre & "*"
    …

Et voici le résultat :