Dernière ligne et la dernière colonne renseignées d'une feuille

[ouskel'n'or]

Comment connaître les numéros des dernière ligne et dernière colonne renseignées d'une plage de données.

Récapitulatif de diverses méthodes possibles :

UsedRange - 1
Donne le nombre de lignes renseignées de la feuille où qu'elle soient
Si les lignes 1 à 10 et 17 à 19 sont vides, mais que les lignes 11 à 16 et 20 à 24 sont renseignées, donne 11 (6 + 5)

Code :

    NbLignes = ActiveSheet.UsedRange.Rows.Count

UsedRange - 2
Donne la première ligne renseignée de la feuille

Code :

    LaPremièreLigne = ActiveSheet.UsedRange.Row

CurrentRegion - 1
Donne la dernière ligne de la Plage
S'il n'y a pas de ligne vide dans la plage de données, que la cellule "Cel" se trouve dans la plage de données, donne la dernière ligne de la plage de données, non de la feuille

Code :

    DerniereLigne = Range(Cel).CurrentRegion.End(xlDown).Row

CurrentRegion – 2
Donne l'adresse de la plage
S'il n'y a pas de ligne vide dans la plage de données, si la cellule "Cel" se trouve dans la plage de données, donne la plage de données.
Ex : Cel = "A15" où "$A$15" et se trouve donc dans la plage A4:N25

Code :

    Plage = Range("A15").CurrentRegion.Address(False, False)  'Ex : "A4:N25"

Range(Col 65536).End(xlUp) (VBA2003 et antérieur)
Donne la dernière ligne de la feuille pour la colonne
Pour connaître la dernière ligne absolue renseignée de la colonne A

Code :

    DerniereLigne = Range("A65536").End(xlUp).Row

Cells(Columns(1).Cells.Count, 1).End(xlUp).Row(Toutes versions y compris 2007)
Donne la dernière ligne de la feuille pour la colonne
Pour connaître la dernière ligne absolue renseignée de la colonne A

Code :

DerniereLigne = Cells(Columns(1).Cells.Count, 1).End(xlUp).Row

Range("A1").End(xlDown)
Si la colonne A n'est pas vide, donne la dernière ligne de la plage de cellules pour la colonne A
Pour connaître la dernière ligne de la plage de cellules renseignées commençant range("A1")

Code :

    DerniereLigne = Range("A1").End(xlDown).Row

Range(Col & NoLigne).End(xlDown).Row
Donne la dernière ligne d'une plage de cellules renseignées à partir de la ligne NoLigne, colonne Col
Pour connaître la dernière ligne de la plage renseignée, colonne C, commençant NoLigne = 15

Code :

    DerniereLigne = Range("C" & NoLigne).End(xlDown).Row

Range(...).SpecialCells(xlCellTypeLastCell)
S'il existe ou non des lignes vides dans dans la feuille, donne la dernière ligne absolue de la feuille
Attention : Si des lignes sont supprimées dans la plage, enregistrer le fichier pour avoir le réel N° de la dernière ligne (idem pour les colonnes supprimées)

Code :

    DerniereLigne = Range("A1").SpecialCells(xlCellTypeLastCell).Row

Range(...).SpecialCells(xlCellTypeLastCell).address
Donne l'adresse absolue de la dernière cellule renseignée de la feuille

Code :

    AdresseAbsolue = Range("A1").SpecialCells(xlCellTypeLastCell).address

Pour les colonnes
C'est pareil… mais pour les colonnes (!)

Code :

1
2
3
4
5
6
    DerniereColonne = ActiveSheet.UsedRange.Columns.Count
    DernièreColonne = Selection.CurrentRegion.End(xlToRight).Column
    DerniereColonne = Range("A1").End(xlToRight).Column
    DerniereColonne = Range("IV1").End(xlToLeft).Column
    'où IV est la dernière colonne d'une feuille de calculs => 256)
    DerniereColonne = Range("A1").SpecialCells(xlCellTypeLastCell).Column

Connaître une plage de cellules
Toutes les combinaisons sont possibles. Exemple :

Code :

1
2
Plage = Range(Cells(1,1), Cells(Range("A65536").End(xlUp).Row, _
                Range("IV1").End(xlToLeft).Column)).address

ou en tant qu'objet, avec une instance :

Code :

1
2
Set Plage = Range(Cells(1,1), Cells(Range("A65536").End(xlUp).Row, _
                Range("IV1").End(xlToLeft).Column))

Code :

Plage = Range("A1:" & Range("A1").SpecialCells(xlCellTypeLastCell).Address).Address

Sélection :

Code :

Range("A1:" & Range("A1").SpecialCells(xlCellTypeLastCell).Address).select

[ouskel'n'or]

Une autre façon de compter :

Code :

1
2
3
4
'Dernière ligne renseignée de la feuille de calculs
DerLig = Worksheets("Feuil1").UsedRange.SpecialCells(xlCellTypeLastCell).Row
'Dernière Colonne de la feuille de calculs
Dercol = Worksheets("Feuil1").UsedRange.SpecialCells(xlCellTypeLastCell).Column

ou bien

Code :

1
2
3
4
5
6
'Dernière ligne renseignée de la feuille de calculs
DerLig = Split(Worksheets("Feuil1").UsedRange.Address, "$")(4)
'donne la lettre de la dernière colonne renseignée de la feuille de calculs
Dercol = Split(Worksheets("Feuil1").UsedRange.Address, "$")(3)
'Donne le numéro de la dernière Colonne de la feuille de calculs
Dercol = Range(Split(Worksheets("Feuil1").UsedRange.Address, "$")(3) & 1).column

Pour une plage commençant en I5 (sans ligne ou colonne entièrement vide)

Code :

1
2
3
4
5
6
7
8
'Dernière ligne renseignée d'une plage homogène de données
DerLig = Split(Range("I5").CurrentRegion.Address, "$")(4)

'donne la lettre de la dernière colonne renseignée de la plage de données
Dercol = Split(Range("I5").CurrentRegion.Address, "$")(3)

'Donne le numéro de la dernière colonne renseignée de la plage de données
Dercol = Range(Split(Range("I5").CurrentRegion.Address, "$")(3) & 1).Column

Juste pour le fun

[ouskel'n'or]

Question récurrente : Que veut dire la ligne suivante

Code :

DerLig = Split(Range("I5").CurrentRegion.Address, "$")(4)

CurrentRegion représente la plage de données solidaire de la cellule désigné (I5 dans l'exemple)
CurrentRegion.address fournit l'adresse absolue de la plage de données cohérente (sans ligne ni colonne entièrement vide) sous la forme
Première colonne de la plage & Première Ligne & ":" & dernière colonne de la plage & dernière ligne de la plage. Ex "$I$5:$AX$2512"

Split convertit un string en tableau en utilisant un séparateur de "mots"

Si on utilise "$" comme séparateur on obtient un tableau d'indices 0 à 4 où

Code :

1
2
3
4
5
6
Tableau = Split("$I$5:$AX$2512", "$")
Tableau(0) => ""
Tableau(1) => "I"
Tableau(2) => 5
Tableau(3) => "AX"
Tableau(4) => "2512"

La ligne citée fournit donc la dernière ligne de la plage de donnée cohérente incluant la cellule I5.
Attention : Si une ou plusieurs cellules renseignées de la ligne 4 sont mitoyennes de cellules renseignées de la ligne 5, CurrentRegion l'inclura dans la plage.

Si on utilise ":" comme séparateur, on obtient un tableau d'indices 0 à 1
Ex "$I$5:$AX$2512"

Code :

1
2
3
Tableau = Split("$I$5:$AX$2512", ":")
Tableau(0) => "$I$5"
Tableau(1) => "$AX$2512"

Tableau(1) donne donc ici l'adresse absolue de la dernière cellule renseignée de la plage

Enfin, si l'on souhaite obtenir la dernière ligne absolue de la plage de donnée :

Code :

DerLig = Split(Worksheets("feuil1").UsedRange.Address, "$")(4)

L'intérêt de ce code est qu'il fournit la dernière ligne absolue de la plage de données, y compris si cette plage comprend des lignes totalement ou partiellement vides.

Attention, si la feuille est totalement vide, les deux derniers codes génèrent une erreur.
Explication :
Adresse = Worksheets("feuille1").UsedRange.Address '=> "$A$1"
Tablo = Split(Worksheets("feuille1").UsedRange.Address, "$")
Ubound(Tablo) '=> 2
Tablo(4) n'existe donc pas
Si le cas est envisageable, utiliser une autre syntaxe ou ajouter la condition :

Code :

If Ubound(Split(Worksheets("feuille1").UsedRange.Address, "$")) = 4 then

[cmako]

Envoyé par ouskel'n'or

Range(...).SpecialCells(xlCellTypeLastCell)
S'il existe ou non des lignes vides dans dans la feuille, donne la dernière ligne absolue de la feuille
Attention : Si des lignes sont supprimées dans la plage, enregistrer le fichier pour avoir le réel N° de la dernière ligne (idem pour les colonnes supprimées)

Code :

    DerniereLigne = Range("A1").SpecialCells(xlCellTypeLastCell).Row

Je me suis fait avoir

Si la feuille est enregistrée avec un filtre (filtre automatique ≠ tout) alors cette formule donne la dernière ligne de la feuille filtrée.

[ouskel'n'or]

Je ne connaissais pas le problème et de toutes façons je n'utilise jamais cette syntaxe qui est la préférée de quelqu'un que je connais. Ma préférée à moi est

DerLig = Split(Worksheets("feuil1").UsedRange.Address, "$")(4)

mais elle nécessite d'avoir vérifié si la feuille n'est pas entièrement vide. Mais là, SilkyRoad a depuis longtemps proposé le code. En substance, sinon de mémoire

Code :

1
2
If Worksheets("feuil1").UsedRange.Address = "$A$1" _
   and Worksheets("feuil1").Range("A1") = "" then 'on sort !

[Pierre Fauconnier]

Ousk,

Pas d'accord avec cela

Code :

Range("A1").End(xlDown) - 1

Donne la dernière ligne de la plage de cellules pour la colonne A
Pour connaître la dernière ligne de la plage de cellules renseignées commençant range("A1")

La cellule renvoyée dépend de A1.

Si A1 est vide, renseigne la première cellule non vide en descendant. Si la colonne est vide, renvoie la dernière cellule de la colonne A65536 ou A1048576

Si A1 n'est pas vide, renvoie la cellule non vide qui précède la prochaine cellule vide en descendant. Si Tout est vide en dessous, renvoie A65536 ou A1048576

[ouskel'n'or]

Ok, je corrige mais la citation complète présente d'ailleurs une contradiction puisque dans la ligne de code le -1 n'y est pas

Range("A1").End(xlDown) - 1
Donne la dernière ligne de la plage de cellules pour la colonne A
Pour connaître la dernière ligne de la plage de cellules renseignées commençant range("A1")

Code :

    DerniereLigne = Range("A1").End(xlDown).Row

Merci de ton attention