[VBA] Déterminer la dernière ligne/colonne d’un tableau

Identifier la dernière cellule non vide d’une colonne

En VBA, une fonction récurrente consiste à déterminer la dernière ligne d’un tableau.
Prenons l’exemple ci-dessous :

Tableau

Pour celles et ceux qui n’ont besoin que des lignes de code, les voici avec leurs descriptions respectives en remarque

'déclaration des variables
Dim derniereLigne As Long
Dim derniereColonne As Integer
Dim myRange As Range
Range("A" & Rows.Count).End(xlUp).Select 'sélection de la dernière cellule non vide de la colonne A
derniereLigne = Range("A" & Rows.Count).End(xlUp).Row 'n° de la dernière ligne non vide de la colonne A
Range("A" & Rows.Count).End(xlUp).Offset(1).Select 'sélection de la première cellule vide de la colonne A
derniereLigne = Range("A" & Rows.Count).End(xlUp).Row + 1 'n° de la première ligne vide de la colonne A
Set myRange = Range("A" & Rows.Count).End(xlUp) 'enregistrement de la dernière cellule non vide de la colonne A dans la variable myRange
myRange.Select 'sélection de la variable myRange
Cells(1, Columns.Count).End(xlToLeft).Select 'sélection de la dernière cellule non vide de la ligne 1
derniereColonne = Cells(1, Columns.Count).End(xlToLeft).Column 'n° de la dernière colonne non vide de la ligne 1
Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Select 'sélection de la première cellule vide de la ligne 1
derniereColonne = Cells(1, Columns.Count).End(xlToLeft).Column + 1 'n° de la première colonne vide de la ligne 1

Explications

Une ligne de code couramment utilisée est la suivante :

Range("A" & Rows.Count).End(xlUp).Select

Cette ligne signifie : sélectionne (.Select) la dernière cellule non vide (.End) vers le haut (xlUp) à partir de la dernière cellule (&Rows.Count) de la colonne A (Range(« A »))
Donc cette ligne de commande se comporte exactement comme si vous vous placiez dans la dernière cellule de la colonne A (cellule A1048576 pour la version 2007 d’Excel) et que vous appuyiez sur CTRL + flêche du haut.

Pourquoi aller du bas de la feuille vers le haut et pas l’inverse ? Tout simplement, pour éviter une erreur de sélection s’il s’avérait que le tableau contient des cellules vides.

Essayez donc le code suivant en effaçant la cellule A3 par exemple : Range("A1").End(xlDown).Select

Ce qu’il faut bien comprendre ici est la signification de Rows.Count
Rows.Count signifie littéralement : dernière ligne.
En l’occurence,il signifie ici, dernière ligne de mon onglet soit la ligne 1048576 comme nous venons de le voir mais il aurait pu aussi s’agir de la dernière ligne d’une plage bien précise (dans cet exemple :
derniereLigne = Range("A1").CurrentRegion.Rows.Count la variable derniereLigne à une valeur égale à 4)

Une variante consiste à récupérer le N° de la dernière ligne non vide d’une colonne dans une variable plutôt que de sélectionner cette dernière ligne :

 Dim derniereLigne As Long
 derniereLigne = Range("A" & Rows.Count).End(xlUp).Row'passez le curseur sur dernièreLigne pour en lire la valeur ou utilisez la commande MsgBox ci-dessous
 MsgBox derniereLigne 

Ici, la variable derniereLigne récupère la valeur 4.
Nous avons, pour cela, légèrement modifié, le code précédent : nous avons remplacé la commande Select par la commande Row.
Ne confondons pas Rows en VBA qui représente une ligne dans Excel avec la commande Row qui représente un N° de ligne.

Pour sélectionner la première cellule vide d’une colonne :

Range("A" & Rows.Count).End(xlUp).offset(1).Select

Pour info :
Offset(1) signifie : décaler de 1 vers le bas
Offset(,2) signifie : décaler de 2 vers la droite
Offset(-3) signifie : décaler de 3 vers le haut
Offset(,-2) signifie : décaler de 2 vers la gauche
Offset(-5,6) signifie : décaler de 5 vers le haut et de 6 vers la droite

Pour obtenir le N° de la première ligne vide d’une colonne :

Dim derniereLigne As Long
derniereLigne = Range("A" & Rows.Count).End(xlUp).Row + 1

Enfin, il est utile de savoir enregistrer l’emplacement de la dernière cellule non vide d’une colonne dans une variable :

Dim myRange As Range
Set myRange = Range("A" & Rows.Count).End(xlUp)
myRange.Select

Ici, l’enregistrement du résultat dans la variable myRange est précédé de la commande Set puisqu’on attribue un « objet » à une variable (ici, une cellule) et non pas une valeur alphanumérique comme on l’a fait dans lors de l’utilisation de la variable derniereLigne.

Identifier la dernière cellule non vide d’une ligne

Sélection de la dernière colonne non vide d’une ligne :

Cells(1, Columns.Count).End(xlToLeft).Select

Ici, nous avons remplacé la syntaxe d’identification de la cellule; Range(« A »&Rows.count) devient Cells(1,Columns.Count) ou 1 est le N° de ligne de la cellule et Columns.Count, le N° de la colonne. Nous avons donc sélectionné la dernière cellule de la ligne 1 et nous avons déplacé le curseur vers la gauche.

N° de la dernière colonne non vide d’une ligne :

Dim derniereColonne As integer
derniereColonne = Cells(1, Columns.Count).End(xlToLeft).Column

Sélection de la première colonne vide d’une ligne :

Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Select

N° de la dernière colonne vide d’une ligne :

Dim derniereColonne As Integer
derniereColonne = Cells(1, Columns.Count).End(xlToLeft).Column + 1

 

Et en réponse au commentaire de Clamar75 qui souhaite déterminer la dernière ligne d’un tableau dont les colonnes auraient des tailles différentes

Range("A" & Rows.Count).End(xlUp).End(xlUp).Offset(Range("A" & Rows.Count).End(xlUp).End(xlUp).CurrentRegion.Rows.Count - 1).Select

Voilà, cette méthode n’a plus de secret pour vous; il en existe d’autres pour identifier la dernière ligne ou la dernière colonne d’un onglet… que nous verrons dans un prochain billet à l’aide de la méthode Find.

14 réactions sur “[VBA] Déterminer la dernière ligne/colonne d’un tableau

  1. Bonjour,
    ce n’est pas le premier article que je rencontre et dont tu es l’auteur. Je te remercie donc ici pour l’ensemble de ta contribution, ton esprit de partage et la richesse que tu apportes à tous ceux qui, comme moi, cherchent avec difficultés le moyen d’en apprendre un peu plus.
    Au regard du chemin parcouru et perdu en temps de lecture inutile, principalement pour le remplacement de l’ancien outils de recherche de fichiers que chacun à remplacé par le FSO qui travaille à la vitesse de l’escargot.
    Je pense que ton article expliquant aux français comment travailler sur les dossiers et fichiers devrait être mieux référencé. D’une part, ces articles se comptent sur les doigts d’une main (30 heures de recherches et de tests) et de l’autre, la clarté de son explication permet au lambda de l’appliquer.
    En effet, une condition principale à la compréhension par nous, foule sentimentale, est que le « code d’exemple » soit fonctionnel car le voir fonctionner au pas à pas nous aide fortement.
    Vous êtes arrivés ici comme moi, et vous pensez aussi avoir perdu beaucoup de temps !
    Je n’ai pas pour habitude de passer le mien à commenter des articles, mais plus ceux-ci seront commentés, mieux ils seront vus par Google.
    A bon entendeur francophone…
    Cordialement,
    Lisamano

    • Merci pour ce commentaire, j’espère que ces quelques articles sont utiles à de nombreuses personnes. Les statistiques concernant les visites sur ces pages dépassent mes espérances, donc je pense que le temps passé à rédiger ces quelques cas de figure n’a pas été vain.

  2. super article, gg dude ! j’étais bloqué merci à toi c’est super continue à partager toutes tes astuces et tes connaissances MERCI!!

  3. bonjour,
    je rame pour sauvegarder un dictionnaire créer sous VBA Excel sur c:\
    put d’un binary ne fonctionne pas
    merci de votre aide
    sinon SUPER SITE

    • Bonjour Claude, désolé de ne pas pouvoir vous aider, cela fait plus d’un an que je n’ai pas ouvert un fichier Excel… posez votre question sur les forums d’Excel Pratique. Bon courage.

  4. J’étais bloqué mais grâce à cet article j’ai terminé mon application avec succès. Merci une autre fois.

  5. C’est bien mais ça ne marche que dans le cas ou la première colonne contient des valeurs jusqu’à la dernière ligne.
    Si on a des données dans un tableau depuis A1 jusqu’à C10, la dernière ligne est la ligne 10.
    Si dans ce tableau la colonne 1 ne contient des valeurs que jusqu’à la ligne 8, la procédure va retourner 8 à la place de 10 !!!!
    N’y a-t-il donc pas une fonction qui donne directement le nombre de ligne d’un tableau ?

    La critique est exactement la même pour trouver la dernière colonne.

    Cdlt

    • La réponse à votre question a été ajoutée en fin d’article.
      Il est, bien entendu, préférable de toujours avoir des tableaux dont le nombre de lignes sont les mêmes pour chacune de leurs colonnes.

Répondre à Clamar75000 Annuler la réponse.

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l’aide de votre compte WordPress.com. Déconnexion /  Changer )

Photo Google

Vous commentez à l’aide de votre compte Google. Déconnexion /  Changer )

Image Twitter

Vous commentez à l’aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l’aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s