[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

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.

Publicités

Le blog d’un passionné d’Excel

… et je me suis dit : pourquoi pas un blog ?!

Récemment très impliqué dans l’écriture de scripts en VBA et contributeur actif sur les forums dédiés à Excel, j’ai créé ce blog afin de partager mes connaissances du VBA dans un cadre plus théorique que celui des forums surtout orientés vers la résolution de problèmes d’ordre pratique.

Le but de ce blog sera, avant tout pédagogique, à savoir qu’il ne se contentera pas de distribuer des listes de code à intégrer dans un script mais prendra le temps de décortiquer le fonctionnement de chacun d’entre eux afin que le lecteur comprenne bien le mécanisme qui se cache derrière chaque commande et qu’il puisse ensuite l’adapter à ses besoins personnels.

Ce blog s’attachera à expliquer des notions de base telles que l’identification de la dernière cellule non vide d’une colonne aussi bien que des principes, au premier abord, plus complexes comme l’utilisation du « dictionnaire » pour une gestion efficace des doublons ou encore l’utilisation des « variables tableau » afin d’intégrer un turbo dans vos programmes.

Avant d’aborder le premier billet, pour celles et ceux qui entendent parler du VBA pour la première fois, je les renvoie au cours d’introduction du site Excel Pratique qui leur permettra d’acquérir une compréhension globale de ce qu’est le VBA.

Pour les autres… le premier billet est ici.

111204-110140