Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Inactif  
    Avatar de ouskel'n'or
    Profil pro
    Inscrit en
    février 2005
    Messages
    12 465
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2005
    Messages : 12 465
    Points : 15 326
    Points
    15 326

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

    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
      10  0

  2. #2
    Inactif  
    Avatar de ouskel'n'or
    Profil pro
    Inscrit en
    février 2005
    Messages
    12 465
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2005
    Messages : 12 465
    Points : 15 326
    Points
    15 326

    Par défaut

    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
      10  0

  3. #3
    Inactif  
    Avatar de ouskel'n'or
    Profil pro
    Inscrit en
    février 2005
    Messages
    12 465
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2005
    Messages : 12 465
    Points : 15 326
    Points
    15 326

    Par défaut

    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
      6  0

  4. #4
    Membre confirmé
    Homme Profil pro
    Inscrit en
    mars 2007
    Messages
    616
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Luxembourg

    Informations forums :
    Inscription : mars 2007
    Messages : 616
    Points : 556
    Points
    556

    Par défaut

    Citation Envoyé par ouskel'n'or Voir le message
    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.
      0  0

  5. #5
    Inactif  
    Avatar de ouskel'n'or
    Profil pro
    Inscrit en
    février 2005
    Messages
    12 465
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2005
    Messages : 12 465
    Points : 15 326
    Points
    15 326

    Par défaut

    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 !
      2  0

  6. #6
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    14 089
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 14 089
    Points : 38 552
    Points
    38 552
    Billets dans le blog
    47

    Par défaut

    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
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------
      1  0

  7. #7
    Inactif  
    Avatar de ouskel'n'or
    Profil pro
    Inscrit en
    février 2005
    Messages
    12 465
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : février 2005
    Messages : 12 465
    Points : 15 326
    Points
    15 326

    Par défaut

    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
      2  0

Discussions similaires

  1. [XL-2007] Copier ligne et coller dernière ligne non vide
    Par John Parker dans le forum Macros et VBA Excel
    Réponses: 30
    Dernier message: 06/09/2013, 16h20
  2. [XL-2010] MACRO pour compter lignes renseignées d'une feuille sans tenir compte du filtrage auto
    Par langeard dans le forum Macros et VBA Excel
    Réponses: 4
    Dernier message: 22/11/2012, 18h06
  3. Réponses: 2
    Dernier message: 05/08/2008, 16h41
  4. supprimer les colonnes masquées d'une feuille
    Par k-eisti dans le forum Macros et VBA Excel
    Réponses: 4
    Dernier message: 03/07/2007, 16h05
  5. Excel VBA - Dernière ligne d'une colonne
    Par sat478 dans le forum Macros et VBA Excel
    Réponses: 1
    Dernier message: 14/12/2005, 17h33

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo