Traitements de tableaux Excel en VBA pour les nettoyer

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


Sujets que vous pourriez aussi aimer :


Purger les tableaux Excel avec du code VBA
Dans cette formation, nous allons voir comment exploiter du code Visual Basic pour Excel afin de nettoyer des tableaux. Ainsi nous serons en mesure de supprimer des lignes vides pour améliorer la présentation ou encore de supprimer des lignes correspondant à des valeurs caractéristiques ou obsolètes. Enfin nous verrons comment supprimer les valeurs redondantes dans des colonnes comme les doublons par exemple. Les boucles nous seront très utiles pour parcourir les tableaux ainsi que d'autres notions que nous avons abordées dans les supports précédents.

Suppression de lignes vides - Purger un tableau

• Télécharger la classeur tableaux-pour-traitements-donnees-vba.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel,

L'extension .xlsm de ce classeur signifie qu'il est en mesure de gérer les macros et donc le code VBA que nous allons développer contrairement à un classeur Excel classique à l'extension .xlsx. Comme vous le remarquez, ce classeur est composé de trois feuilles. La feuille Doublons servira à tester le code permettant d'éliminer les valeurs redondantes d'un tableau. La feuille valeurs car permettra de tester le code Visual Basic permettant de supprimer des lignes de valeurs correspondant à un critère. Et enfin la feuille lignes vides destinée à tester la macro VBA détectant les lignes sans contenu à purger automatiquement.

• Activer la feuille lignes vides en cliquant sur son onglet,

Nous souhaitons que le code soit capable de traiter les tableaux d'une feuille en détectant automatiquement les bornes et les lignes vides à supprimer.

• Basculer dans l'éditeur de code Visual Basic par ALT + F11 par exemple,
• Dans la fenêtre Projet sur la gauche, double cliquer sur Feuil1 (lignes vides),
• Dans sa feuille de code qui apparaît au centre, créer la procédure, suppr_vides(),

Sub suppr_vides()

End Sub

Notre premier challenge avant de penser à purger les lignes vides des tableaux est d'être capable de détecter leurs bornes pour les parcourir avec une boucle de programmation. L'objet Cells en VBA désigne globalement une ou des cellules de la feuille. Cet objet possède une propriété particulière SpecialCells qui permet de pointer la dernière cellule non vide de la feuille lorsqu'on lui passe l'argument xlCellTypeLastCell. Cette dernière cellule non vide est celle pour laquelle les indices de lignes et de colonnes sont les plus grands. C'est pourquoi les propriétés dérivées Column et Row de cette propriété SpecialCells permettent d'indiquer respectivement les indices de colonne et de ligne de cette dernière cellule. Nous avons besoin de deux variables pour mémoriser les indices de ligne et de colonne de cette dernière cellule. Nous les nommerons der_ligne et der_colonne. De même nous avons besoin de deux variables que nous nommerons ligne et colonne pour parcourir les tableaux.

• Déclarer ces quatre variables comme des entiers :

Dim der_ligne As Integer: Dim der_colonne As Integer
Dim ligne As Integer: Dim colonne As Integer

Notez la présence des deux points permettant d'énumérer la déclaration de variables sur une même ligne. La formation sur la déclaration de variables en VBA aborde ces notions. Il s'agit maintenant de mémoriser les indices de ligne et de colonne de la dernière cellule de la feuille dans ces variables grâce à la propriété SpecialCells de l'objet Cells.

• Pour ce faire, ajouter les deux affectations suivantes :

der_colonne = Cells.SpecialCells(xlCellTypeLastCell).Column
der_ligne = Cells.SpecialCells(xlCellTypeLastCell).Row

Nous devons de même initialiser les variables ligne et colonne sur les indices de la première cellule de la feuille.

• Pour ce faire, ajouter les affectations suivantes :

ligne = 1: colonne = 1

Pour parcourir l'ensemble des cellules en partant de la première jusqu'à la dernière non vide, à la recherche des bornes des tableaux, nous allons utiliser une double boucle While. Pour cela, nous empruntons un bout de code réalisé dans le support de formation qui présente ces boucles de programmation.

• A la suite du code, saisir la double boucle suivante :

While (ligne < der_ligne)
colonne = 1

While (colonne < der_colonne)

colonne = colonne + 1
Wend

ligne = ligne + 1
Wend

While (ligne < der_ligne) : Tant que l'indice de ligne pour parcourir les cellules n'a pas atteint la ligne de la dernière cellule non vide. colonne = 1 : A chaque passage dans cette boucle, nous réaffectons la variable colonne à 1, car la boucle imbriquée qui suit, déplace cet indice jusqu'au dernier. While(colonne < der_colonne) : tant que l'indice de colonne n'a pas atteint la colonne de la dernière cellule non vide. colonne = colonne + 1 : A chaque passage dans la boucle, il ne faut pas oublier d'incrémenter la variable, sinon la boucle tourne sur place et ne s'arrête jamais. ligne = ligne + 1 : C'est la même chose pour la boucle faisant varier les indices de ligne des cellules. Wend : Il s'agit du mot clé qui permet de fermer l'instruction de la boucle. Avec cette double boucle imbriquée, nous parcourons l'ensemble des cellules de la feuille comprises entre la toute première et la dernière non vide.

La condition qui permet de déceler que nous sommes sur une cellule vide à l'intérieur d'un tableau n'est pas seulement le fait que la cellule soit vide. Il faut vérifier en même temps qu'elle possède une bordure sur la gauche. Ainsi nous saurons que nous pouvons supprimer la ligne entière. L'instruction de programmation qui permet de poser des critères à vérifier est l'instruction If() en VBA traitée dans un précédent support. Comme il y a deux critères à vérifier ensemble, nous utiliserons le mot clé And dans les parenthèses de l'instruction If().

• A l'intérieur de la seconde boucle imbriquée dans la première, ajouter le code suivant :

If (Cells(ligne,colonne).Borders(xlEdgeLeft).LineStyle = xlContinuous And Cells(ligne,colonne).Value = '') Then
MsgBox ligne & ' ' & colonne
End If

L'objet Cells fait référence à une cellule de la feuille, donc Cells(ligne,colonne) désigne la cellule en cours de lecture dans la double boucle. Nous utilisons sa propriété Borders pour savoir si le trait de sa bordure sur la gauche existe (Borders(xlEdgeLeft).LineStyle= xlContinuous). Et en même temps, grâce à la propriété Value de l'objet Cells, nous vérifions si son contenu est vide (Cells(ligne,colonne).Value = ''). Si les deux conditions sont vérifiées ensemble, pour l'instant nous n'engageons pas d'action, nous nous contentons d'afficher à l'écran ces indices de ligne et de colonne à titre de vérification. Avant de tester ce code, nous allons lui associer un bouton sur la feuille :

• Revenir sur la feuille lignes vides,
• Activer le ruban Développeur,
• Cliquer sur le bouton Insérer,
• Dans la liste, choisir le premier bouton de formulaire et le tracer sur la feuille,
• Dans la boîte de dialogue qui se déclenche, sélectionner la macro suppr_vides,
• Valider par Ok et changer le texte sur le bouton,
• Cliquer ensuite sur le bouton pour exécuter le programme,

Si le ruban développeur n'apparaît pas sur votre interface, la formation pour débuter en VBA Excel apprend comment l'intégrer sur la fenêtre.


La boîte de dialogue MsgBox que nous avons codée, se déclenche en effet dès qu'une ligne vide est trouvée en renvoyant les indices de ligne et de colonne. Maintenant que nous avons la confirmation que notre code détecte bien les lignes vides, nous allons pouvoir exploiter les propriétés et méthodes de l'objet Cells qui permettent de désigner et supprimer une ligne complète. Mais avant cela, nous allons créer une copie de la feuille lignes vides pour juger de la différence à l'issue. Pour ce faire :

• Cliquer avec le bouton droit de la souris sur l'onglet lignes vides,
• Dans le menu contextuel, choisir Déplacer ou copier,
• Dans la fenêtre qui suit, cocher la case Créer une copie tout en bas,
• Puis, sélectionner (en dernier) dans la liste et valider par Ok.

Vous obtenez ainsi une copie intégrale de la feuille en cours, placée en dernière position des feuilles du classeur. La propriété EntireRow de l'objet Cells permet de désigner la ligne complète de la cellule. Sa méthode Delete permet alors de supprimer cette ligne.

• Dans le code, remplacer la ligne du MsgBox par la suivante :

Cells(ligne, colonne).EntireRow.Delete

• Enregistrer les modifications (CTRL + S),
• Revenir sur la feuille et cliquer sur le bouton de Macro,

Le code semble fonctionner mais pas parfaitement. En effet vous constatez qu'une ligne vide subsiste. En fait, c'est tout à fait logique. Dans ce tableau, il existait une séquence de plusieurs lignes vides à la suite. Quand le code supprime la première ligne vide qu'il rencontre, Excel sélectionne automatiquement la suivante et notre boucle incrémente la variable ligne. Donc nous nous retrouvons deux lignes plus bas et la ligne vide consécutive n'est pas traitée. Cela induit que lorsque nous supprimons une ligne par le code, nous devons revenir une ligne plus haut en décrémentant la variable ligne.

• Dans le code, sous l'instruction qui supprime la ligne vide, ajouter le code suivant :

ligne = ligne - 1

• Effectuer un copie-collé du tableau de la feuille dupliquée à la place du tableau de la feuille lignes vides de manière à le retrouver dans son état d'origine,
• Puis cliquer de nouveau sur le bouton.

Cette fois c'est parfait, toutes les lignes vides sont supprimées, y compris les lignes vides consécutives. Ce petit code est donc intéressant pour purger les tableaux de la sorte. Le code dans son intégralité, relativement simple vous en conviendrez, est le suivant :

Sub suppr_vides()
Dim der_ligne As Integer: Dim der_colonne As Integer
Dim ligne As Integer: Dim colonne As Integer

der_colonne = Cells.SpecialCells(xlCellTypeLastCell).Column
der_ligne = Cells.SpecialCells(xlCellTypeLastCell).Row
ligne = 1: colonne = 1

While (ligne < der_ligne)
colonne = 1

While (colonne < der_colonne)
If (Cells(ligne, colonne).Borders(xlEdgeLeft).LineStyle = xlContinuous And Cells(ligne,colonne).Value = '') Then
Cells(ligne, colonne).EntireRow.Delete
ligne = ligne - 1
End If

colonne = colonne + 1
Wend

ligne = ligne + 1
Wend
End Sub

Supprimer les valeurs caractéristiques d'un tableau
Ici nous souhaitons créer une macro capable de supprimer les lignes du tableau lorsque la différence en pourcentage est inférieure à 5%, soit 0,05 en valeur numérique. Vous l'avez compris, il s'agit d'une mise en pratique de ce que nous avons appris précédemment. Deux choses sont simplement à adapter. La première consiste à débuter la lecture du tableau à partir d'un indice de ligne et de colonne précis. La seconde consiste à modifier le critère car il ne s'agit plus de débusquer les lignes vides.

• Dupliquer la feuille valeurs car comme nous l'avons appris précédemment,
• Basculer dans l'éditeur de code Visual Basic,
• Sélectionner et copier intégralement le code de la procédure suppr_vides(),
• Dans la fenêtre Projet, double cliquer sur Feuil2 (valeurs car),
• Coller le code,
• Modifier le nom de la macro en suppr_val(),
• Initialiser les variables ligne et colonne toutes deux à 6 (ligne = 6: colonne = 6),

En effet la lecture du tableau doit se faire sur les valeurs en pourcentage dans la colonne F, en commençant par la cellule F6. Comme la colonne ne change pas, la double boucle n'est plus nécessaire.

• Supprimer l'instruction colonne = 1 en début de première boucle,
• Supprimer le début de la boucle imbriquée : While (colonne < der_colonne),
• Supprimer l'incrémentation de la boucle imbriquée : colonne = colonne + 1,
• Supprimer la fermeture de la boucle imbriquée : Wend,

Les critères de l'instruction If() doivent être adaptés. Ils doivent vérifier en même temps que la valeur de la cellule est inférieure ou égale à 5% (Cells(ligne,colonne).Value<=0.05) et que cette cellule n'est pas vide (Cells(ligne,colonne).Value<>''). En effet une cellule vide renverrait à l'issue d'un tableau, une valeur toujours inférieure à 0.05. La boucle tournerait sur place et ne s'arrêterait jamais.

• Adapter les critères de l'instruction If() selon les remarques faites ci-dessus :

If (Cells(ligne, colonne).Value <= 0.05 And Cells(ligne, colonne).Value <> '') Then

Le reste du code ne change pas. Il faut en effet supprimer la ligne entière dans ce cas et décrémenter la variable ligne.

• Créer un bouton de formulaire sur la feuille valeurs_car,
• Lui associer la macro suppr_val(),
• Cliquer sur ce bouton pour le tester.

Toutes les valeurs dont le pourcentage est effectivement inférieur à 5% disparaissent avec la ligne entière. Il s'agit d'un traitement intéressant si le tableau comporte de nombreuses lignes. Le code, très court, lui ne change pas, il s'adapte aux bornes.


Le code complet est présenté ci-dessous :

Sub suppr_val()
Dim der_ligne As Integer
Dim ligne As Integer: Dim colonne As Integer

der_ligne = Cells.SpecialCells(xlCellTypeLastCell).Row
ligne = 6: colonne = 6

While (ligne < der_ligne)
If (Cells(ligne, colonne).Value <= 0.05 And Cells(ligne, colonne).Value<>'') Then
Cells(ligne, colonne).EntireRow.Delete
ligne = ligne - 1
End If

ligne = ligne + 1
Wend
End Sub

Supprimer les valeurs répétitives d'un tableau
Nous souhaitons purger le tableau de la feuille Doublons de toutes ses valeurs redondantes sur la base de la colonne CODES. Pour faciliter le travail, nous allons employer une astuce. Nous allons trier le tableau par ordre croissant sur la première colonne. Ainsi tous les doublons seront regroupés les uns sous les autres. Ensuite nous allons parcourir les lignes de cette première colonne. Puis, nous allons comparer la valeur de la cellule active avec la cellule du dessus. Si les deux valeurs sont égales, nous supprimerons la ligne.

• Dupliquer la feuille Doublons en dernière position,
• Dans l'éditeur de code, double cliquer sur Feuil3 (Doublons) depuis la fenêtre Projet,
• Créer la procédure suppr_doublons(),
• Saisir les déclarations usuelles :

Dim ligne As Integer: Dim colonne As Integer
ligne = 2: colonne = 2

Nous connaissons le point de départ du tableau (B2 soit ligne= 2: colonne = 2). Pour la dernière cellule non vide, nous n'avons plus besoin de la propriété SpecialCells de l'objet Cells. Nous allons parcourir les cellules tant qu'elles ne sont pas vides. Mais avant cela, de façon à regrouper toutes les valeurs répétitives, nous allons trier le tableau en VBA.

• Ajouter la ligne de code suivante afin de faire un tri croissant du tableau :

Range('B2').SortRange('B2'), xlAscending, Header:=xlYes

L'objet Range comme vous le savez permet de designer une plage de cellules ou plus simplement une cellule comme ici, en l'occurrence la première cellule du tableau. Sa méthode Sort permet de réaliser le tri du tableau désigné par cette cellule de référence. Ce tri s'effectue selon les paramètre qui sont listés et séparés d'une virgule. xlAscending : pour réaliser un tri croissant. Header:=xlYes : pour indiquer que ce tableau comporte bien une ligne d'entête qui ne doit pas être intégrée dans le tri. Souvenez-vous, la touche F1 du clavier sur la méthode sélectionnée permet d'afficher l'aide de cette dernière. Vous y trouvez ainsi sa syntaxe et tous les arguments que vous pouvez lui passer. Le critère d'incrémentation de la boucle While doit donc être changé.

• Modifier la boucle While comme suit :

While Cells(ligne, colonne).Value <> ''

Wend

Dans cette boucle, maintenant que les valeurs sont triées, le principe consiste à vérifier que la valeur de la cellule en cours est identique à la valeur de la cellule du dessus. Si c'est le cas, il s'agit d'un doublon et nous devons supprimer la ligne entière afin de purger le tableau.

• Modifier le critère du If() comme suit :

If (Cells(ligne, colonne).Value = Cells(ligne - 1,colonne).Value) Then

End If

Le reste ne change pas, à l'intérieur du If() nous supprimons la ligne à l'aide de la méthode Delete. Le code complet est donc le suivant :

Sub suppr_doublons()
Dim ligne As Integer: Dim colonne As Integer

ligne = 2: colonne = 2

Range('B2').Sort Range('B2'), xlAscending, Header:=xlYes

While Cells(ligne, colonne).Value <> ''
If (Cells(ligne, colonne).Value = Cells(ligne - 1, colonne).Value) Then
Cells(ligne, colonne).EntireRow.Delete
ligne = ligne - 1
End If

ligne = ligne + 1
Wend
End Sub

• Créer un bouton sur la feuille doublons et l'associer à cette macro,
• Cliquer sur le bouton pour le tester.

Le tableau est réduit à sa plus simple expression, purgé de tous les doublons. Ce code est d'autant plus intéressant qu'il est capable de traiter des tableaux de très grande taille sans que le code ne change.