Détection de la dernière ligne:ProblèmeRésolu

Question

Bonjour,
J'ai lu que pour trouver la dernière cellule d'une plage utilisée, il fallait
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
et donc, pour définir la dernière ligne:
l = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

Mais ceci donne la dernière cellule de la zone utilisée, même si cette cellule est vide (cas d'une dernière cellule dans laquelle ont a mis une valeur, avant de revenir dans cette cellule pour supprimer ladite valeur)

Le problème de l = Cells(Rows.Count, 1).End(xlUp).Row, est que cela détermine (très bien, au demeurant) la dernière cellule non vide de la première colonne, ou de la 2ème si Cells(Rows.Count, 1) est remplacé par Cells(Rows.Count, 2) etc... Mais faire la recherche colonne par colonne en comparant les différents l peut vite devenir lourd...

Quel programme simple peut définir à tout coup la dernière cellule non vide d'un grand tableau. Ce tableau a plusieurs colonnes (dont le nombre est connu et fixe), beaucoup de lignes, des cellules vides. Les lignes sont susceptibles de varier en nombre dans le temps, mais je cherche à déterminer la dernière ligne à un moment donné...

Merci d'avance

cs_MPi · Accepted Answer

Si la page n'est pas vide et que tu veux savoir combien de lignes et de colonnes il y a, tu fais comme suit. 

Si tu as des données sur 20 lignes et que la ligne 25 est rouge, ça te retournera 20 comme nombre de lignes.

Si tu utilises UsedRange, ça te retournera 25, même chose avec SpecialCells(xlCellTypeLastCell)

Et tu mets ces procédures où bon te semble...


Sub MaSub()

    Dim nbLignes As Long, nbColonnes As Long

    

    nbLignes = Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row

    nbColonnes = Cells.Find("*", Range("A1"), , , xlByColumns, xlPrevious).Column

    

    MsgBox "La dernière ligne contenant des données est la ligne " & nbLignes & vbCrLf & _

            "et
la dernière colonne contenant des données est la colonne " & Chr(64
+ nbColonnes)

End Sub


Et si tu veux savoir combien de lignes il y a dans une colonne particulière (ici B)


Sub MaSub2()

    Dim nbLignes As Long

    

    nbLignes = Columns("B:B").Find("*", Range("B1"), , , xlByRows, xlPrevious).Row

    

    MsgBox "La dernière ligne contenant des données est la ligne " & nbLignes

End Sub

MPi

Molenn · Answer

Au lieu d'utiliser SpecialCells(xlCellTypeLastCell), tu remplaces par 2 instructions successives :
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
En positionnant ta cellule active sur la cellule (1,1) de ton tableau.

J'ai eu le même problème que toi avec LastCell, et depuis que j'ai mis ces 2 là, je n'ai plus aucun souci.

Molenn

cs_Papayaga · Answer

Merci de ta réponse, 
 mais cela ne marche pas, car la recherche s'arrête à la première cellule vide, par exemple au niveau C4, car A4 est vide et D4 également... 
Or A5 a une valeur... et bien d'autres cellules au dessous, dans la colonne A, ou B, ou C...

cs_Papayaga · Answer

Re
J'ai peut-être trouvé une solution avec ceci:
Sub Dernière_cellule()

Dim l, li as integer
Cells(1, 1).Select
l = 1


For i = 1 To 3
    li = Cells(Rows.Count, i).End(xlUp).Row


    If li > l Then
       l = li
    End If
    
Next
               ' et l est la dernière ligne
End Sub

Il faut que je teste cela ...

Molenn · Answer

Une solution pourrait être, mais c'est de la bidouille hein ...
Dans un premier temps, tu testes la dernière ligne avec
Selection.End(xlDown).address, ce qui te remonte les coordonnées de ta dernière ligne non vide.
Tu enregistres cette valeur dans une variable.
Et tu relances un Selection.End(xlDown).address
Tu testes l'adresse, si elle est différente de $A$65536, c'est que la valeur dans ta variable n'était pas la dernière ligne, etc ...

En fait, il faut garder à l'esprit dans ton tableau que quand tu fais un ctrl+flèche bas (l'équivalent de Selection.End(xlDown).select), ça va jusqu'à la dernière cellule vide. Et quand tu l'as effectivement atteint, la prochaine fois que tu fais ctrl+flèche bas, tu vas tout en bas du fichier Excel, soit la ligne 65536.

Avec une boucle, tu reproduis ce schéma en gardant dans une variable l'adresse de la cellule avant de lancer le code.

Faire ensuite la même chose pour les colonnes.

Ce n'est sans doute pas optimum ni génial mais bon ^^

Autre option : Tu peux avoir des lignes entièrement vides, ou seulement certaines cellules ?
Si aucune ligne ne peut être vide, tu peux lancer le code
Selection.End(xlDown).address
à partir de la première ligne de chaque colonne, et tu conserves l'adresse de cellule qui a le n° de ligne le plus élevé ?

Molenn

cs_Papayaga · Answer

Re
En fait, il faut garder à l'esprit dans ton tableau que quand tu fais un ctrl+flèche bas (l'équivalent de Selection.End(xlDown).select), ça va jusqu'à la dernière cellule vide. Et quand tu l'as effectivement atteint, la prochaine fois que tu fais ctrl+flèche bas, tu vas tout en bas du fichier Excel, soit la ligne 65536.

Non: ctrl+flèche bas renvoie à la dernière cellule qui précède une cellule vide... et comme j'ai différents groupes de cellules pleines séparés par une ou des cellules vides => problème.
par contre je n'ai pas de ligne entière vide, Chaque ligne a quelquepart une cellule pleine. C'est ce qui détermine qu'elle fait partie du tableau.
Je recherche la ligne qui contient la dernière cellule contenant une valeur. Ce qui équivaut à dire que le tableau s'arrête à cette ligne, après il n'y a plus de cellule pleine.

Non, la solution Selection.End(xlDown) ne me paraît pas bonne. 

Par contre Sub Dernière_cellule() que j'ai écrit plus haut me paraît correctement fonctionner. Mais il faut que je l'éprouve plus dans toutes les configurations...

LUSTRUCRU · Answer

Bonsoir,
Essaye cela, tu selectionnes  bien ta derniere ligne

Dim LigneSuivante As Long
LigneSuivante = Application.WorksheetFunction.CountA(Range("A:A")) + 1
JF

cs_Papayaga · Answer

Re

LigneSuivante = Application.WorksheetFunction.CountA(Range("A:A")) + 1

Cela ne marche pas, la ligne trouvée se situant au niveau de la 2ème cellule vide de la première colonne. De plus cela n'explore que la colonne A:A.

LUSTRUCRU · Answer

Modifie ta plage de recherche:

Dim LigneSuivante As Long
LigneSuivante = Application.WorksheetFunction.CountA(Range("A:X")) +1

Chez moi cela fonctionne sur un tableau de 6700 Lignes sur 35 Colonnes

mortalino · Answer

Salut, essai mes fonctions : ' dans un module : Dim MaCellule As Range ' *** Variable à placer dans les déclarations Public Function PremiereLigneVide(CelluleDepart As Range, _ MonOrdre As XlSearchOrder, MaDirection As XlSearchDirection) As Long If MonOrdre = xlByRows Then PremiereLigneVide = _ Columns(MaCellule.Column).Find("", MaCellule, , , MonOrdre, MaDirection).Row Else PremiereLigneVide = _ Columns(MaCellule.Column).Find("", MaCellule, , , MonOrdre, MaDirection).Column End If End Function ____________________________________________________________________________ Private Sub ExempleUtilisation() Sheets(1).Select Set MaCellule = [A65536] ' *** Il vous suffit de modifier la cellule de départ, pour la recherche, entre [] MsgBox PremiereLigneVide(MaCellule, xlByRows, xlPrevious) ' *** Ici, le 2ème et 3ème argument ont 2 possibilités proposées ' 2ème : xlByRows ou xlByColumns ' 3ème : xlNext ou xlPrevious End Sub Coloration syntaxique automatique [mortalino] @++

--Mortalino-- Le mystérieux chevalier, "Provençal, le Gaulois" /DIV>

cs_Papayaga · Answer

Merci de ta réponse Mortalino... Mais j'ai besoin d'un peu plus d'éclaircissement. En effet, en ayant mis tes deux programmes dans un module, une première erreur de syntaxe apparaît 
[i]Public Function PremiereLigneVide(CelluleDepart As Range, _
    MonOrdre As XlSearchOrder, MaDirection As XlSearchDirection) As Long/i
Mais peut-être que je m'y suis mal pris... auquel cas précise où je dois mettre tout cela.

Par ailleurs, mon programme Dernière_cellule fonctionne bien, mais j'ai un nombre connu de colonnes (dans l'exemple: 3, d'où for i = 1 to 3)

Il semblerait que ton programme ne necessite pas de connaitre précisemment  ni le nombre de colonnes, ni celui des lignes... ce qui est un plus (à condition de savoir le faire fonctionner)

cs_Papayaga · Answer

Merci de ta réponse Mortalino... Mais j'ai besoin d'un peu plus d'éclaircissement. En effet, en ayant mis tes deux programmes dans un module, une première erreur de syntaxe apparaît 
Public Function PremiereLigneVide(CelluleDepart As Range, _
    MonOrdre As XlSearchOrder, MaDirection As XlSearchDirection) As Long
Mais peut-être que je m'y suis mal pris... auquel cas précise où je dois mettre tout cela.

Par ailleurs, mon programme Dernière_cellule fonctionne bien, mais j'ai un nombre connu de colonnes (dans l'exemple: 3, d'où for i = 1 to 3)

Il semblerait que ton programme ne necessite pas de connaitre précisemment  ni le nombre de colonnes, ni celui des lignes... ce qui est un plus (à condition de savoir le faire fonctionner)

mortalino · Answer

La fonction PremiereLigneVide doit absolument être placé dans un Module.

Ensuite, quand tu cherches la première ligne vide de la colonne A, il faut partir de A56536, pour B, c'est B65536, etc...
Exemple depuis un UserForm ou autre Module :

Dim MaCelluleA  As Range
Dim MaCelluleB  As Range
Dim MaCelluleC  As Range
Dim DerLineA    AsLong
Dim DerLineB    As Long
Dim DerLineC    As Long

Sheets(1).Select 
 Set MaCelluleA = [A65536] 
 Set MaCelluleB = [B65536] 
 Set MaCelluleC = [C65536] 
 
 DerLineA = PremiereLigneVide(MaCelluleA, xlByRows, xlPrevious)

 DerLineB = PremiereLigneVide(MaCelluleB, xlByRows, xlPrevious)
 DerLineC = PremiereLigneVide(MaCelluleC, xlByRows, xlPrevious)

@++ 

<hr width="100%" size="2" />
  --Mortalino--
Le mystérieux chevalier, "Provençal, le Gaulois"
/DIV>

mortalino · Answer

Humm pour ton problème de syntaxe, corrige comme ceci :

Public Function PremiereLigneVide(CelluleDepart As Range, MonOrdre As XlSearchOrder, MaDirection As XlSearchDirection) As Long

(le copier / coller devrait fonctionner)
@++ 

<hr width="100%" size="2" />
  --Mortalino--
Le mystérieux chevalier, "Provençal, le Gaulois"
/DIV>

cs_Papayaga · Answer

La nouvelle écriture de la Public fonction ne déclenche plus les foudres d'excel
Cela marche, mais idem qu'avec "ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)": Admettons que la ligne 10 soit la première ligne après la fin du tableau,  si j'efface le contenu de toutes les cellules de la dernière ligne du tableau (c'est à dire la ligne 9 et avec la touche <-), le programme me donne toujours la ligne 10 (et non la 9 qui est devenue la première ligne vide après la fin du tableau)
Par contre, si je mets une valeur dans une cellule de la ligne 12, par exemple dans cells(12,3), le programme me détecte bien la ligne 13 comme première ligne après la fin du tableau

"Ensuite, quand tu cherches la première ligne vide de la colonne A, il faut partir de A56536, pour B, c'est B65536, etc..." ===> Je n'ai pas besoin de cela: le programme trouve la bonne ligne, même si la dernière cellule se trouve dans une autre colonne.

Non, le plus embêtant c'est qu'il pose le même problème que "ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)"===> Cf mon premier message

cs_Papayaga · Answer

Merci MPi!!!...
Cela marche impeccable, dans les différentes configurations de mes tableaux.
De plus, le code est très simple et ne suppose pas qu'on connaisse une des variables (nombre de colonnes, ou nombre de lignes)
Bravo!

aegnor78 · Answer

super info!  

même si je ne comprend pas très bien comment çà marche ...

merci

Détection de la dernière ligne:Problème

17 réponses

Votre réponse

Discussions similaires