Comment connaître la ligne suivante après un tableau en VBA Excel ?

Dans ce tutoriel, nous allons découvrir plusieurs méthodes permettant d’obtenir le numéro de la ligne sur laquelle se trouve la première cellule non vide. Cela nous permettra par exemple d’ajouter une nouvelle entrée dans une base de données.

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. Introduction

Lorsque nous développons une macro en VBA, il est très courant de devoir connaître le numéro de la dernière ligne ou colonne utilisée sur la feuille de calcul, afin par exemple de pouvoir inscrire une nouvelle ligne de données.

Pour cela, nous pouvons utiliser plusieurs méthodes, lesquelles vont dépendre du type de recherche que nous souhaitons mettre en place.

2. Obtenir la première cellule non vide d’une colonne

Si nous disposons d’une base de données dans laquelle il n’y a aucune cellule vide, nous pouvons atteindre la dernière cellule d’une colonne en particulier en sélectionnant la première cellule de la colonne, puis en utilisant le raccourci clavier [Ctrl]+[Flèche du bas] :

Pour réaliser la même opération en VBA, nous pouvons utiliser la méthode :

Sub selectionDerniereCellule()
    [A8].End(xlDown).Select
End Sub

Pour en obtenir le numéro de la ligne correspondante, nous pouvons appeler la propriété Row :

Msgbox [A8].End(xlDown).Row

Si en revanche la colonne contient des cellules vides, utiliser cette ligne de commande nous retournera le numéro de la ligne sur laquelle se trouve la première cellule vide :

Nous allons donc devoir effectuer l’opération inverse, à savoir partir de la dernière ligne de la feuille, ou au moins d’une ligne située suffisamment bas dans la feuille de calcul pour être certain que la base de données n’atteindra jamais cette dernière.

Pour rappel sur les anciennes versions d’Excel, c’est-à-dire jusqu’au millésime 2003, les feuilles de calculs étaient constituées de 65 536 lignes.

À partir d’Excel 2007, ce nombre de lignes passe à 1 048 576.

Ici, la ligne de code devient alors :

Msgbox [A50000].End(xlUp).Row

3. Obtenir la première cellule non vide d’une ligne

Bien entendu, nous pouvons utiliser ces mêmes instructions pour obtenir la dernière cellule non vide de la ligne et changeant tout simplement le sens du décalage.

Pour obtenir la dernière cellule d’une ligne ne contenant aucune cellule vide, nous effectuons un décalage vers la droite :

Msgbox [A50000].End(xlUp).Row

Et si la ligne contient des cellules vides, nous pourrons donc effectuer un décalage vers la gauche en partant d’une cellule située au loin sur la ligne :

Msgbox [Z8].End(xlLeft).Row

4. Obtenir la cellule suivante

Maintenant que nous connaissons les coordonnées de la dernière cellule non vide d’une colonne ou d’une ligne, nous pouvons obtenir sans trop de difficulté le numéro de la ligne correspondante ajoutant +1 :

Msgbox [A50000].End(xlUp).Row+1

Si nous souhaitons sélectionner cette cellule, nous effectuerons plutôt un décalage de cellule avec l’instruction Offset :

[A50000].End(xlUp).Offset(1, 0).Select

5. Ajouter une nouvelle entrée

Maintenant que nous connaissons le numéro de la ligne, nous pouvons ajouter une nouvelle entrée en insérant ce numéro dans une variable :

Sub nouvelleEntree()
    Dim ligne As Integer
    ligne = [A50000].End(xlUp).Row + 1
    
    Cells(ligne, 1) = "16/01/2022"
    Cells(ligne, 2) = "Nina"
    Cells(ligne, 3) = "Nord"
    Cells(ligne, 4) = "10000"
    
End Sub

5. Sélectionner la dernière cellule utilisée d’une feuille

Si nous souhaitons sélectionner la dernière cellule utilisée d’une plage de cellule, nous pouvons utiliser l’objet UsedRange.

Cet objet de type Range retourne en effet une plage englobant toutes les cellules situées entre la toute première et la toute dernière cellule utilisée.

Pour sélectionner la dernière cellule utilisée du classeur nous pouvons donc utiliser :

Sub selectionnerDerniereCellule()
    With ActiveSheet.UsedRange
        .Cells(.Rows.Count, .Columns.Count).Select
    End With
End Sub

Attention toutefois, cette macro permet de sélectionner la toute dernière cellule utilisée sur le classeur. Or, cette dernière cellule utilisée ne correspond pas forcément à la dernière cellule dans laquelle se trouve une donnée.

En effet, imaginons que nous saisissions une valeur comme un numéro de téléphone dans une cellule située hors de la plage retournée par UsedRange, puis que nous changions la mise en forme de cette cellule :

Maintenant, même si nous supprimons la valeur saisie dans cette cellule, la macro renverra toujours les coordonnées de cette cellule :

Il faut supprimer cette cellule avec tous ces paramètres (valeur, mise en forme,…) pour éviter ce retour incorrect :