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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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] :

Excel formation - dernière ligne et colonne en vba - 01

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 :

Excel formation - dernière ligne et colonne en vba - 02

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 :

Excel formation - dernière ligne et colonne en vba - 03

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

Excel formation - dernière ligne et colonne en vba - 04

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

Excel formation - dernière ligne et colonne en vba - 05

 

 

 

 



Articles qui pourraient vous intéresser

Comment identifier les cellules identiques en 1 clic sur Excel (couleur selon valeur) ?
Comment créer un tableau de bord qui s’actualise et identifie automatiquement les meilleures performances sur Excel !
L’erreur cachée qui plombe vos tableaux Excel (et comment l’éviter)
Comment entourer automatiquement cellules non conformes Excel sans MF conditionnelle ni formule ?
Comment créer un tableau de bord commercial intelligent avec Excel ?
Comment créer un graphique dessiné à la main avec Excel ?
Comment créer un graphique de distribution en 5 minutes chrono ?
L’astuce Excel pour imprimer le tableau que vous voulez en un clic !
Comment gérer ses comptes sur Excel avec le « Suivi du budget familial »
Comment créer facilement des QR codes sans VBA avec Excel ?
Transformez vos données en graphique tornade interactif facilement sur Excel !
Comment créer un graphique Iceberg dans Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.