Discussion :

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

Sélectionner tout - Visualiser dans une fenêtre à part

    NbLignes = ActiveSheet.UsedRange.Rows.Count

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

    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 :

Sélectionner tout - Visualiser dans une fenêtre à part

    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 :

Sélectionner tout - Visualiser dans une fenêtre à part

    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 :

Sélectionner tout - Visualiser dans une fenêtre à part

    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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

    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 :

Sélectionner tout - Visualiser dans une fenêtre à part

    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 :

Sélectionner tout - Visualiser dans une fenêtre à part

    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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Sélection :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[ouskel'n'or]

Une autre façon de compter :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[cmako]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

    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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Merci de ton attention