VBA pour Excel : Réorganiser 3 cellules d’une colonne en 3 colonnes distinctes

Description de l'article de blog :

EXCEL VBA

Thomas Espitalier

6/8/202612 min read

Excel est un outil puissant pour gérer et analyser des données, mais certaines tâches répétitives peuvent rapidement devenir chronophages. Aujourd’hui, je vous propose de découvrir comment automatiser la réorganisation de données à l’aide d’une macro VBA. Plus précisément, nous allons créer un script qui permet de transformer une colonne de données en trois colonnes distinctes, en répartissant les lignes trois par trois.

Ce type de macro est particulièrement utile si vous travaillez avec des listes longues et que vous souhaitez les restructurer pour une meilleure lisibilité ou pour les adapter à un format spécifique (par exemple, pour une impression ou une présentation). Nous sommes en effet parfois obligé d'utiliser Des tableaux initialement mal structuré, lorsqu'ils nous viennent de collègues, de partenaires extérieurs à notre entreprise, ou de plateformes numériques sur lesquelles nous faisons des extractions au format Excel.

Le problème à résoudre

Imaginons que vous ayez une colonne A dans la Feuil1 dont l'entête se nomme "coordonnées" et qui est un enchaînement de nom, adresse et téléphone et qui passe à la personne suivante toutes les 3 lignes. Votre objectif est de répartir les noms, adresses et téléphones en trois colonnes (A, B, C) dans la Feuil2, en prenant trois lignes consécutives de la Feuil1, à partir de la ligne 2 pour éviter de prendre l'entête de la colonne où est écrit "coordonnées", et en les plaçant sur une seule ligne dans la Feuil2 à partir également de la ligne 2 pour ne pas effacer les entêtes de colonne "Nom", "adresse" et "téléphone", comme ci-dessous. Dans cet exemple je n'ai pas renommé les 2 feuilles.

Pourquoi utiliser VBA ?

Gain de temps : Une macro VBA exécute cette tâche en quelques secondes, même pour des milliers de lignes.
Précision : Évite les erreurs humaines liées à une manipulation manuelle.
Flexibilité : Le script peut être adapté à d’autres besoins (par exemple, répartir 4 ou 5 lignes à la fois).
Automatisation : Vous pouvez déclencher cette macro à l’ouverture du fichier ou via un bouton.

Le code VBA expliqué

Pour suivre ce tutoriel Excel VBA en même que moi, commencez à ouvrir un classeur vierge. Dans la première Feuille qui s'appelle Feuil1, tapez coordonnées et ajoutez-y une suite de noms, adresses et téléphones comme ci-dessous :

Puis dans la Feuil2 tapez seulement les entêtes de colonnes "noms", "adresses" et "téléphones" dans A1, B1, C1 comme ci-dessous :

Enregistrez toute de suite le fichier au format `.xlsm` (Format Excel avec Macro), cela évitera de perdre votre travail si Excel plante, et rouvrez-le.

Avant de continuer, si vous n'avez pas encore le l'onglet "Développeur" à côté de l'onglet "Aide" dans le Ruban Excel, vous devez l'ajouter en allant dans l'onglet "Fichier" puis en bas à gauche en cliquant sur "Options", puis choisissez la catégorie "Personnaliser le Ruban", puis cochez la case "Développeur".

Pour commencer à taper votre script VBA, vous devez ouvrir votre console VBA en allant dans le ruban "Développeur", puis en cliquant tout à gauche sur "Visual Basic". La fenêtre suivante s'ouvre :

Il est possible de faire fonctionner votre script sur une seule Feuille de votre classeur, par exemple Feuil1 ou Feuil2 dans l'aperçu ci-dessus, ou sur l'ensemble du classeur, c'est à dire "ThisWorkbook". Si vous voulez que votre script soit exécuté dès l'ouverture du classeur, choisissez de tapez votre script dans ThisWorkbook. Pour cela double-cliquer sur ThisWorkbook.

Dans le menu déroulant où il y a écrit (Général), choisissez Workbook. Automatiquement le 2e menu déroulant où il y a écrit (Déclaration) va se positionner sur "Open", et le script suivant va apparaître :

C'est la première ligne et la dernière ligne de notre script. Nous devons maintenant compléter le script entre ces 2 lignes.

Nous allons procéder par étapes :
1 - tout d'abord, nous allons faire un test avec une sorte de copier-coller d'une cellule de la Feuil1 vers la Feuil2, par exemple la cellule A2 de la Feuil1 vers la cellule A2 de la Feuil2.
2 - le 2e test sera le copier-coller d'une seule colonne, par exemple la colonne A de la Feuil1 vers la colonne A de la Feuil2
3 - Le 3e test va être de copier-coller seulement les noms de la colonne A de la Feuil1 vers la colonne A de Feuil2 en évitant de copier-coller les adresses et téléphones
4 - Enfin nous ferons l'objectif de notre tutoriel, mettre les noms, adresses et téléphones de la Feuil1 dans 3 colonnes différentes de la Feuil2

Etape 1

Pour pouvoir manipuler La valeur de la cellule A2 de la Feuil2, nous devons accéder à la valeur de cette cellule, même si elle est encore vide, par le code suivant :
Private Sub Workbook_Open()
Worksheets("Feuil2").Cells(2, 1).Value
End Sub
Dans ce code nous avons Worksheets("Feuil1") qui correspond à la Feuil1, nous avons Cells(2, 1) qui correspond à la cellule ligne 2 colonne 1, c'est à dire la colonne A, et Value qui ne récupère seulement la valeur à l'intérieur de la cellule, et non le format comme la couleur d'arrière plan ou les bordures.

Pour modifier la valeur de la Cellule A2, il suffit d'ajouter le signe "égale" est la valeur voulue. Par exemple, pour ajouter le nom "Dumoulin" dans la cellule A2, voici le code :
Private Sub Workbook_Open()
Worksheets("Feuil2").Cells(2, 1).Value = "Dumoulin"
End Sub
Pour tester ce nouveau code, vous devez fermer la console VBA, puis enregistrer et fermer le classeur, puis ouvrez à nouveau le classeur.

Maintenant, nous allons remplacer le nom "Dumoulin" que nous avons écrit à la main dans le code par le contenu de la cellule A2 de la Feuil1 cette fois :
Private Sub Workbook_Open()
Worksheets("Feuil2").Cells(2, 1).Value = Worksheets("Feuil1").Cells(2, 1).Value
End Sub
Vous pouvez tester à nouveau ce code.

Etape 2

Maintenant nous allons copier-coller seulement 5 lignes de la Feuil1 vers la Feuil2. Nous pourrions créer le code suivant :
Private Sub Workbook_Open()
Worksheets("Feuil2").Cells(2, 1).Value = Worksheets("Feuil1").Cells(2, 1).Value
Worksheets("Feuil2").Cells(3, 1).Value = Worksheets("Feuil1").Cells(3, 1).Value
Worksheets("Feuil2").Cells(4, 1).Value = Worksheets("Feuil1").Cells(4, 1).Value
Worksheets("Feuil2").Cells(5, 1).Value = Worksheets("Feuil1").Cells(5, 1).Value
Worksheets("Feuil2").Cells(6, 1).Value = Worksheets("Feuil1").Cells(6, 1).Value
End Sub

Cependant si notre colonne contient les données de 2000 personnes, cela correspondraient à beaucoup de lignes de code. C'est pour cela que nous allons utiliser une boucle qui va répéter la même ligne de code. Mais si nous gardons le code tel quel, nous allons rester 5 fois sur la même ligne sans descendre d'une ligne à l'autre. Il existe plusieurs types de Boucle en langage VBA, la plus simple à utiliser est la boucle For. Mais la boucle For ne peut pas toute seule passer d'une ligne à l'autre sans créer une valeur de Ligne qui puisse varier à chaque répétition de la boucle, que l'on va appeler variable. Nous pouvons donner le nom que l'on souhaite à cette variable, mais les espaces et les accents sont interdits. Dans le code suivant j'ai appelé cette variable "varLigne" :

Private Sub Workbook_Open()
For varLigne = 2 To 6
Worksheets("Feuil2").Cells(varLigne, 1).Value = Worksheets("Feuil1").Cells(varLigne, 1).Value
Next
End Sub

Nous allons maintenant améliorer cette boucle pour qu'elle ne s'arrête pas au bout de la 6e ligne, mais lorsque la première ligne vide est trouvée. Pour cela, nous n'allons pas utiliser la boucle For, mais la boucle While : tant que la cellule suivante a une valeur, c'est à dire qu'elle est différente (<>) de rien (""), alors on continue. Dans la boucle For, la variable VarLigne était initialisée à 2 pour éviter d'effacer les entêtes de colonne "coordonnées" en Feuil1 et "noms" en Feuil2, directement après le For. Nous ne pouvons pas faire la même chose juste après While, C'est pour cela que nous initialisons la variable (VarLigne = 2) sur la ligne de code précédente While :

Private Sub Workbook_Open()
varLigne = 2
While Worksheets("Feuil1").Cells(varLigne, 1).Value <> ""
Worksheets("Feuil2").Cells(varLigne, 1).Value = Worksheets("Feuil1").Cells(varLigne, 1).Value
varLigne = varLigne + 1
Wend
End Sub

Etape 3

Nous allons maintenant copier-coller uniquement les noms des personnes qui se trouvent toutes les 3 lignes dans la colonne A de la Feuille à partir de la 2e ligne pour éviter de copier-coller l'entête de colonne :

L'inconvénient est que nous avons dans la Feuil2 deux cellules vides entre chaque nom.
Pour que tous les noms se suivent dans la Feuil2, nous sommes obligés de faire la différence entre les lignes de la Feuil1 et les lignes de la Feuil2, en créant 2 variables distinctes : varLigFeuil1 et varLigFeuil2. varLigFeuil1 doit toujours trouver les noms de la Feuil1 de 3 lignes en 3 lignes

Etape 4

Pour mettre les noms, adresses et téléphones de la colonne A de la Feuil1 dans 3 colonnes différentes A, B et C de la Feuil2, le plus simple est de reprendre la boucle While du script précédent que l'on va répéter 3 fois. Il suffit dans chaque boucle While de modifier le numéro de colonne dans
Cells(numeroLigne, numeroColonne) et de réinitialiser les variables varLigFeuil1 et varLigFeuil2 entre chaque boucle While, pour revenir en haut de la colonne A de la Feuil1 dans le but de la parcourir une 2e fois puis une 3e fois, tout en se décalant d'une ligne à chaque fois en démarrant la 2e fois à la ligne 3 (1er adresse) puis la 3e fois à la ligne 4 (1er téléphone) :

Private Sub Workbook_Open()
varLigFeuil1 = 2
varLigFeuil2 = 2
While Worksheets("Feuil1").Cells(varLigFeuil1, 1).Value <> ""
Worksheets("Feuil2").Cells(varLigFeuil2, 1).Value = Worksheets("Feuil1").Cells(varLigFeuil1, 1).Value
varLigFeuil1 = varLigFeuil1 + 3
varLigFeuil2 = varLigFeuil2 + 1
Wend
varLigFeuil1 = 3
varLigFeuil2 = 2
While Worksheets("Feuil1").Cells(varLigFeuil1, 1).Value <> ""
Worksheets("Feuil2").Cells(varLigFeuil2, 2).Value = Worksheets("Feuil1").Cells(varLigFeuil1, 1).Value
varLigFeuil1 = varLigFeuil1 + 3
varLigFeuil2 = varLigFeuil2 + 1
Wend
varLigFeuil1 = 4
varLigFeuil2 = 2
While Worksheets("Feuil1").Cells(varLigFeuil1, 1).Value <> ""
Worksheets("Feuil2").Cells(varLigFeuil2, 3).Value = Worksheets("Feuil1").Cells(varLigFeuil1, 1).Value
varLigFeuil1 = varLigFeuil1 + 3
varLigFeuil2 = varLigFeuil2 + 1
Wend
End Sub

Ce script ci-dessus fonctionne, mais cela demande à Excel de parcourir 3 fois la colonne A de la Feuil1. Or si cette colonne contient par exemple 3000 lignes, cela veut dire qu'Excel doit parcourir au total 9000 lignes. Pour une question d'optimisation et de rapidité du Script, il est donc préférable que les noms, adresses et téléphones soient copier-coller tout de suite dans 3 colonnes différentes en parcourant la colonne A de la Feuil1 en une seule fois. Pour cela, nous sommes obligés de faire répéter une boucle qui parcours les 3 colonnes de la Feuil2 à chaque fois qu'une boucle se répète pour parcourir chque ligne de la Feuil1. Ces 2 boucles doivent donc être imbriquées comme dans le script suivant :

Voici ci-dessous le script que nous allons utiliser et que je vais expliquer au paragraphe suivant.

Private Sub Workbook_Open()

varLig1 = 5
varLig2 = 2
varCol2 = 1

While Worksheets("Feuil1").Cells(varLig1, 1).Value <> ""
For var3Lig1 = varLig1 - 3 To varLig1 - 1
Worksheets("Feuil2").Cells(varLig2, varCol2).Value = Worksheets("Feuil1").Cells(var3Lig1, 1).Value
varCol2 = varCol2 + 1
Next
varLig1 = varLig1 + 3
varCol2 = 1
varLig2 = varLig2 + 1
Wend

End Sub

Au lieu d'imbriquer 2 boucles While, j'ai préféré imbriquer une boucle For dans une boucle While. En effet, une boucle For est mieux indiquée pour faire un nombre de tours fixes, dans notre cas 3 pour parcourir les 3 colonnes. De plus, j'ai améliorer le script en créant 2 variables pour les lignes de la Feuil1, varLigF1 et var3LigF1, cette dernière étant initialisée directement dans la boucle For, au lieu d'une seule variable. En effet, il est important de vérifier si les données de la personne suivante sont complétés ou non (et qui commence à la 5e ligne ou se trouve son nom) pour savoir si on a déjà parcouru toute la colonne jusqu'à la fin, avant de revenir 3 lignes en arrière pour mettre les données de la colonne précédente dans 3 colonnes différentes. Cette vérification s'éffectue juste à droite du While, c'est cette première ligne de la boucle qui est à l'avance de 3 lignes par rapport à la boucle For qui suit.

Explications détaillées

Voici le même script avec des explications intégrées au script en commentaire. Ces commentaires sont en rouge pour vous expliquer chaque ligne de code et pour que vous puissiez les adapter selon vos besoins. Le code de script avec les commentaires peuvent être copier-coller ensemble dans la console VBA sans que cela perturbe le fonctionnement du code.

Private Sub Workbook_Open() 'début du bloc macro qui se déclenche dès l'ouverture du classeur

'pour simplifier le script je ne type pas les variables que j'initialise directement ci-dessous

varLig1 = 5 'initialisation de la variable que j'ai nommée varLig1 qui permet de conserver en mémoire et modifier au fur et à mesure de l'exécution du script les numéros de Ligne de la Feuille 1 à partir de la ligne 5 (cela nous permettra de vérifier tout de suite grâce à la boucle While ci-dessous s'il y a une ligne vide après les coordonnées de la personne précedente que la boucle While aurait déjà redistribué en 3 colonnes dans la Feuil2)
var3Lig1 = 2 'initialisation de la variable que j'ai nommée var3Lig1 qui permet aussi de conserver en mémoire et modifier au fur et à mesure de l'exécution du script les numéros de Ligne de la Feuille 1, tout comme varLig1 ci-dessus, (mais cette fois-ci non pas en enchainant les lignes les unes après les autres, mais de 3 lignes en 3 lignes comme nous le verrons dans la boucle While ci-dessous, et seulement à partir de la ligne 2 de la Feuil1 pour éviter de compter l'entête de colonne "coordonnées")
varLig2 = 2 'initialisation de la variable que j'ai nommée varLig2 qui permet de conserver en mémoire et modifier au fur et à mesure de l'exécution du script les numéros de Ligne de la Feuille 2 à partir de la ligne 2
varCol2 = 1 'initialisation de la variable que j'ai nommée varCol2 qui permet de conserver en mémoire et modifier au fur et à mesure de l'exécution du script les numéros de colonne de la Feuille 2 à partir de la colonne 1 (c'est à dire la colonne A)

While Worksheets("Feuil1").Cells(varLig1, 1).Value <> "" 'Cette boucle While permet de répéter les lignes de script ci-dessous précédent Wend jusqu'à ce que la première cellule vide de la colonne A de la Feuil1 soit trouvée
For var3Lig1 = varLig1 - 3 To varLig1 - 1 'Cette boucle For permet de répéter 3 fois le script ci-dessous jusqu'à Next
Worksheets("Feuil2").Cells(varLig2, varCol2).Value = Worksheets("Feuil1").Cells(var3Lig1, 1).Value
'C'est une sorte de copier-coller des cellules de Feuil1 vers les cellules de la Feuil1 : dans la Feuil1 colonne A, toutes les 3 cellules nous prenons les valeurs de ces cellules pour les mettre dans des cellules de la Feuil2 dans des lignes qui se suivent cette fois, d'abord dans la colonne A de la Feuil2, puis à chaque fois que la boucle For se répète, dans la colonne B puis la colonne C de la Feuil2, grâce au code ci-dessous
varCol2 = varCol2 + 1 'la valeur de la variable varCol2 augmente à chaque tour de boucle For, pour passer de la colonne A à la colonne B, puis de la colonne B à la colonne C
Next 'renvoie la suite de l'exécution du script à la première ligne de la boucle For pour répéter 3 fois l'ajout du contenu de la Feuil1 dans 3 colonnes différentes de la Feuil2. Next permet aussi en même temps d'augmenter automatiquement la valeur de la variable varLig1 juste après le "To" de la première ligne de la boucle "For", il n'y a donc pas besoin d'incrémenter varLig1 comme nous l'avons fait avec varCol2 dans la ligne précédente
varLig1 = varLig1 + 3 'dans la Feuil1 nous passons directement de la ligne 2 à la ligne 5, puis de la 5 à la 8, et ainsi de suite
varCol2 = 1 'dans la Feuil2, une fois que la boucle For s'est répétée 3 fois, la variable varCol2 correspond à la colonne C. Nous devons donc revenir à la colonne A en lui donnant à nouveau la valeur 1 (tout en passant à la ligne suivante, ce que nous faisons dans la ligne de code ci-dessous)
varLig2 = varLig2 + 1 'dans la Feuil2 nous passons à la prochaine ligne
Wend 'l'exécution du script revient à la première ligne de la boucle while tant qu'aucune cellule vide n'est trouvée dans la Feuil1 de 3 lignes en 3 lignes

End Sub 'fin du bloc macro

Option 1 : La macro s’exécutera automatiquement à l’ouverture (car l'évènement codé juste après Private Sub est `Workbook_Open`).
Option 2 : Pour l’exécuter manuellement, ajoutez un bouton dans Excel et associez-le à cette macro.