Ajouter une ligne vide sur deux en VBA Excel

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


Sujets que vous pourriez aussi aimer :


Intercaler des lignes vides

Nous avions déjà démontré une technique intéressante avec Excel pour insérer automatiquement une ligne vide sur deux dans un tableau. Ici, pour aérer la présentation ou pour créer des lignes de synthèse, nous proposons de construire une procédure VBA réalisant le même travail. Ainsi, si vous l'enregistrez dans le modèle (Personal.xlsb), vous disposerez d'un bouton capable d'aérer n'importe quel tableau de n'importe quel classeur.

Classeur Excel à télécharger
Pour appuyer le développement, nous suggérons de récupérer un classeur hébergeant un tableau doté de nombreuses lignes.

• Télécharger le classeur intercaler-lignes-vierges.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 un tableau recensant des activités de sorties. Il s'étend sur cinq colonnes et plusieurs centaines de lignes. Il présente donc une configuration idéale pour la mise en oeuvre de ce type de processus automatisé.



Sur la droite, vous notez la présence d'un bouton ActiveX. Il est déjà lié à une procédure VBA. Mais bien sûr, elle est vierge de code pour l'instant. Après avoir sélectionné le tableau à reconfigurer, c'est au clic sur ce bouton qu'une ligne vide sur deux doit être insérée.

Les variables VBA
Pour développer ce code, nous avons tout d'abord besoin de déclarer quelques variables, notamment pour piloter la plage du tableau mais aussi pour connaître le nombre de ses lignes, dans le but de les parcourir toutes.

• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour activer son ruban,
• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,
• Dès lors sur la feuille, double cliquer sur le bouton intitulé aérer,

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

• Dans cette procédure, ajouter les déclarations et affectations suivantes :

...
Dim nbLignes As Integer: Dim i As Integer
Dim plage As Range

Set plage = Selection
nbLignes = plage.EntireRow.Count
...

Nous déclarons la variable nbLignes comme un entier. Son rôle est de récolter le nombre de lignes qu'offre le tableau à aérer. Nous utiliserons la variable i comme compteur de boucle afin de parcourir chacune des lignes du tableau. C'est en effet en les sélectionnant tour à tour que nous serons en mesure d'insérer des lignes entre celles qui existent déjà. Enfin, nous déclarons la variable plage comme un objet de type Range. Il doit en effet représenter la plage de cellules du tableau.

C'est la raison pour laquelle nous l'initialisons (Set) dans l'enchaînement sur la sélection de l'utilisateur grâce à l'objet VBA Excel Selection. Comme vous le savez, il est très simple de sélectionner intégralement un tableau, même volumineux. Il suffit de cliquer sur l'une de ses cellules puis de réaliser le raccourci clavier CTRL + A. Dès lors, nous exploitons la propriété enfant Count de la propriété EntireRow sur cet objet de plage de cellules, pour stocker le nombre de lignes à traiter dans la variable nbLignes.

Débuter à partir de la troisième ligne
Avec la technique de sélection que nous avons évoquée précédemment, ce n'est pas forcément la première ligne du tableau qui est active par défaut. Selon le clic de départ de l'utilisateur, il peut s'agir de n'importe quelle ligne. Et d'ailleurs, nous ne souhaitons pas débuter l'insertion des lignes vides à partir de la première ligne, mais à partir de la troisième. Les insertions se réalisent en effet au-dessus de la ligne active. Ainsi, nous ajouterons une première ligne vierge entre la deuxième et la troisième ligne puisque bien sûr, celle des entêtes ne doit pas être touchée.

• A la suite du code, ajouter les instructions VBA suivantes :

...
If (nbLignes > 5) Then

plage.Cells(3, 1).Select

Else
MsgBox "Vous devez d'abord sélectionner un tableau.", vbInformation
End If
...

En cas d'oubli de sélection par l'utilisateur, nous testons tout d'abord le nombre de lignes sélectionnées. Arbitrairement, nous considérons, si la sélection ne comporte pas au moins six lignes (>5), que le traitement doit être avorté d'où l'envoi d'un message avec la fonction MsgBox, dans la branche Else de l'instruction conditionnelle.

En cas de sélection acceptée en revanche, nous exploitons l'objet Cells sur la plage. Il permet de raisonner relativement à cette dernière. Et c'est ainsi que nous sélectionnons (Select) la cellule placée sur la troisième ligne de la première colonne (Cells(3, 1)) dans cette plage.

Parcourir chaque ligne
Maintenant et puisque nous connaissons le nombre de lignes à analyser, nous pouvons les parcourir toutes. Une insertion de ligne doit intervenir un coup sur deux. Pour réaliser ces déplacements au cours de l'analyse, un objet de type Range offre la méthode Offset. Elle est l'homologue de la fonction Excel Decaler pour déplacer le pointeur en ligne ou en colonne.

• Dans la première branche de l'instruction conditionnelle, ajouter les lignes suivantes :

...
For i = 1 To nbLignes
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).Select
Next i

Range("A1").Select
...

Nous parcourons les lignes de la sélection de la première (1) à la dernière (nbLignes). A chaque passage dans cette boucle, sur la ligne (EntireRow) de la cellule active (ActiveCell), nous réalisons une insertion (Insert). Une insertion se produit au-dessus de la ligne active et cette dernière devient sélectionnée par défaut. C'est la raison pour laquelle, nous déplaçons l'étude deux lignes plus bas (Offset(2, 0)) pour le prochain passage dans la boucle. De cette manière, la prochaine insertion aura lieu entre les deux lignes suivantes.

Créer les lignes vierges
Le code VBA est terminé et nous n'avons plus qu'à le tester.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Dans le ruban Développeur, cliquer sur le bouton Mode création pour le désactiver,
• Cliquer dans l'une des cellules du tableau, par exemple sur la cellule D8,
• Réaliser le raccourci clavier CTRL + A pour le sélectionner intégralement,
• Puis, cliquer sur le bouton Aérer pour lancer le processus,

Vous voyez les insertions automatisées se produire à grande vitesse. A l'issue, le focus est rendu au haut de la feuille. De plus, vous pouvez apprécier l'insertion effective d'une ligne vide sur deux dans un tableau pourtant volumineux.