Comment ajouter automatiquement une ligne lors d’un changement de valeur dans une colonne Excel

Aujourd’hui je vous propose de répondre à la question posée par Plessier Hervé dans l’espace de commentaires sur la chaîne YouTube, qui souhaite savoir comment insérer une ligne vide lorsqu’une valeur contenue dans une cellule vient à être modifiée. Nous verrons pour cela qu’une simple macro-commande va nous permettre de réaliser cette opération.

Téléchargement

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

Tutoriel Vidéo

1. Présentation

Comme nous l’avons vu dans l’introduction, je vais vous montrer comment insérer automatiquement une nouvelle ligne lorsqu’une valeur change dans une colonne.

Cela va nous permettra par exemple de pouvoir insérer simplement un total ou un dénombrement.

Pour illustrer ce tutoriel, nous allons partir de l’exemple suivant, dans lequel nous retrouvons une liste de clients d’une entreprise :

Ici, nous allons vouloir dénombrer directement dans la base de données le nombre de personne en fonction de l’année depuis laquelle ils sont client de l’entreprise.

Bien entendu, pour réaliser cette opération, nous allons devoir passer par le développement d’une macro-commande en VBA.

Mais pas de panique, nous allons détailler chacune de lignes de code, afin que ce tutoriel soit accessible à tous.

Bien entendu, si vous souhaitez en savoir davantage sur le développement en VBA, je vous invite à suivre la formation dédiée à l’apprentissage de VBA pour les débutants en suivant ce lien.

2. Créer une macro-commande en VBA

La première chose à faire pour pouvoir créer une macro-commande en VBA est de lancer l’éditeur de code VBE.

Il s’agit de l’application intégrée au sein de la suite Office qui permet de créer des macro-commandes VBA.

Pour cela, nous avons plusieurs possibilités, la plus simple étant d’utiliser le raccourci clavier [Alt]+[F11] :

Ensuite, pour saisir les lignes de code, nous allons devoir ajouter un module qui est en quelques sortes une feuille blanche dans laquelle nous allons coder les macro-commandes.

Pour ajouter un module, nous nous rendons dans le menu Insertion > Module.

Cela étant fait, nous pouvons créer la macro en utilisant le mot-clé Sub, suivi du nom que nous souhaitons lui donner, par exemple ajouterTotal :

Sub ajouterTotal()
 
End Sub

Lorsque nous validons la création de la procédure en appuyant sur la touche [Entrée] du clavier, VBE ajoute automatiquement la ligne End Sub qui marque la fin de celle-ci.

Ainsi, tout ce que nous allons saisir entre ces deux lignes va s’exécuter automatiquement à chaque fois que nous allons lancer la macro.

3. Compter le nombre de répétitions des années

Pour commencer, nous allons créer une première variable dans laquelle nous allons insérer la première cellule que sur laquelle va porter l’analyse.

Il s’agit dans notre exemple de la cellule C8 :

    Dim cellule As Range
    Set cellule = Range("C8")

Puis pour passer en revue toutes les années, nous allons utiliser une boucle Do While, qui permet de répéter une série d’instructions tant qu’une condition est remplie (à savoir ici, tant que la valeur de la cellule étudiée contient une valeur, ce qui permet d’analyser toutes les cellules de la colonne).

    Do While cellule <>  ""
    
    Loop

Attention de ne surtout pas lancer la macro à ce moment là sous peine de faire planter Excel.

En effet, ici la cellule en cours d’analyse est la cellule C8, laquelle contient une valeur.

Ensuite, nous lancer la boucle, sans modifier la cellule analysée.

La conséquence en est alors que la macro va continuellement analyser cette seule et même cellule, la condition sera donc toujours VRAI, et par conséquent, sans fin !

La première chose à faire dans cette boucle sera donc de passer à la cellule de la ligne suivante (il s’agira en réalité de la dernière opération à réaliser…)

Pour cela nous utilisons l’instruction Offset qui permet d’affecter à la variable cellule la cellule située juste en dessous :

Set cellule = cellule.Offset(1, 0)

Nous pouvons maintenant lancer la macro : il ne se passera absolument rien ici, mais au moins elle ne plantera plus !

Pour vérifier qu’il se passe malgré tout quelque chose, nous pouvons afficher la valeur de la cellule en cours d’analyse dans la fenêtre d’exécution en utilisant l’instruction Debug.Print :

Maintenant, nous allons ajouter une nouvelle variable dans laquelle nous allons stocker l’année en cours :

    Dim annee As Integer
    annee = cellule.Value

Puis dans la boucle, nous comparons la valeur de cette variable avec la valeur de la cellule analysée en utilisant une instruction If… Then :

        If cellule <> annee Then
            
        End If

Tant que nous y sommes, juste après cette instruction, nous mettons à jour la valeur de la variable annee :

        If cellule <> annee Then
            
        End If
        annee = cellule.Value

À partir de là, nous allons pouvoir créer un compteur du nombre de fois qu’une année donnée se répète :

    Dim compteur As Integer
    compteur = 0

Que nous allons endsuite insérer dans la boucle :

        If cellule <> annee Then
            Debug.Print annee, compteur
            compteur = 1
        Else
            compteur = compteur + 1
        End If

Ici, lorsque l’année change, nous affichons l’année, puis le nombre de fois que celle-ci se répète en utilisant le Debug.Print.

En revanche, si l’année reste inchangée, alors nous augmentons simplement le compteur d’une unité !

Nous pouvons maintenant lancer la macro et consulter la fenêtre d’exécution pour en visionner le résultat :

4. Ajouter une nouvelle ligne

Maintenant que nous sommes en mesure de savoir :

• Le nombre de lignes,
• Si les années ont changé

Nous allons pouvoir insérer une nouvelle ligne entre les années avec le compteur de lignes :

        If cellule <> annee Then
            
            cellule.EntireRow.Insert Shift:=xlDown
            …
            compteur = 1
        Else 
        …

Cette instruction permet d’insérer une nouvelle ligne entre les deux années lors d’un changement en décalant les lignes situées en dessous vers le bas :

Le curseur reste quant à lui toujours sur la cellule représentée par la variable cellule, qui s’est décalée également vers le bas (sur la première cellule contenant l’année 1975).

Au besoin, nous pouvons maintenant insérer des textes sur cette ligne nouvellement crée, mais au préalable, nous allons remonter le curseur d’une cellule vers le haut en utilisant l’instruction Offset (puis nous le redescendrons juste après) :

            Set cellule = cellule.Offset(-1, 0)
            
            Range("A" & cellule.Row) =  "Année " & annee
            Range("C" & cellule.Row) =  compteur & " Ligne(s)"
            
            cellule.EntireRow.Font.Bold = True
            
            Set cellule = cellule.Offset(1, 0)

Nous avons ici :

• En colonne A : nous affichons le numéro de l’année
• En colonne C : nous affichons le nombre de lignes dénombrées
• Nous en profitons également pour passer toute la ligne en gras

Et pour finir, comme nous l’avons vu à l’instant nous replaçons la cellule d’analyse sur la ligne du dessous.

Ne reste plus qu’à tester la macro en appuyant sur la touche [F5] pour visionner le résultat sur la feuille de calcul :