Ramener la dernière ligne non vide de la feuille (ligne filtrée, masquée ou non)

[david84]

Bonjour,
le but de la manœuvre est de ramener en VBA la dernière ligne non vide d'une feuille de calcul, et ce quel que soit le cas de figure.
Cette feuille peut en effet comporter une ou plusieurs lignes masquées (ou filtrées si l'on parle d'une plage de cellule).
Les méthodes Cells.SpecialCells(xlCellTypeLastCell) et Cells.Find ne ramènent pas le résultat attendu lorsque la dernière ligne non vide est masquée.
J'y suis finalement parvenu en utilisant la méthode Evaluate et en travaillant sur le UsedRange de la feuille mais peut-être y-a-t-il plus simple.
Avez-vous une autre solution ?
A+

 

[ROGER2327]

Re : Ramener la dernière ligne non vide de la feuille (ligne masquée ou non)

Bonjour david84.

Avec UsedRange, sans formule Excel :​

Sub DerLigneNonVide()
Dim DCel As Long

    With Feuil1.UsedRange: DCel = .Row + .Rows.Count - 1: End With
    
    MsgBox DCel
    
End Sub

Bonne journée.


ROGER2327
#6627

Lundi 16 Palotin 140 (Déploration de Saint Achras, éleveur de Polèdres - fête Suprême Quarte)
16 Floréal An CCXXI, 5,8099h - consoude
2013-W18-7T13:56:37Z

 

[david84]

Re : Ramener la dernière ligne non vide de la feuille (ligne masquée ou non)

Bonjour Roger et merci pour votre participation,
le problème avec UsedRange tel que vous l'avez utilisé c'est qu'il ne semble pas toujours donner le bon résultat.
Exemple : faites apparaître la ligne 18 et effacez la valeur présente puis lancez votre code : chez moi il me ramène toujours 18 alors que cette ligne est maintenant vide.
Ce n'est qu'après avoir sélectionné la ligne et clic droit=>supprimer que UsedRange donne à nouveau le bon résultat.
Est-ce la même chose chez vous ?
A+

 

[youky(BJ)]

Re : Ramener la dernière ligne non vide de la feuille (ligne masquée ou non)

Bonjour à tous,
Ceci doit faire l'affaire, ici sur la feuille active
Bruno

MsgBox Cells.Find("*", [A1], , , 1, 2).Row

 

[david84]

Re : Ramener la dernière ligne non vide de la feuille (ligne masquée ou non)

Bonjour,

Bonjour à tous,
Ceci doit faire l'affaire, ici sur la feuille active
Bruno

MsgBox Cells.Find("*", [A1], , , 1, 2).Row

Non : le résultat indique la ligne 5 alors qu'il existe des données filtrées en lignes 6 et 7 et une donnée masquée ligne 18.
A+

 

[ROGER2327]

Re : Ramener la dernière ligne non vide de la feuille (ligne masquée ou non)

Re...

(...)
le problème avec UsedRange tel que vous l'avez utilisé c'est qu'il ne semble pas toujours donner le bon résultat.
Exemple : faites apparaître la ligne 18 et effacez la valeur présente puis lancez votre code : chez moi il me ramène toujours 18 alors que cette ligne est maintenant vide.
Ce n'est qu'après avoir sélectionné la ligne et clic droit=>supprimer que UsedRange donne à nouveau le bon résultat.
Est-ce la même chose chez vous ?
(...)

Non.
Détail de la manipulation faite :

1. Téléchargement de votre classeur.
2. Ouverture du classeur.
3. Enregistrement sous un autre nom.
4. Installation du code.
5. Démasquage de la ligne 18.
6. Effacement du "z".
7. Exécution du code.

Résultat : 7.

Je n'ai hélas pas d'explication.
​

Bon courage !


ROGER2327
#6628

Lundi 16 Palotin 140 (Déploration de Saint Achras, éleveur de Polèdres - fête Suprême Quarte)
16 Floréal An CCXXI, 6,9896h - consoude
2013-W18-7T16:46:30Z

 

[Guest]

Re : Ramener la dernière ligne non vide de la feuille (ligne filtrée, masquée ou non)

Bonjour, David, Roger, Youki

J'ai jamais réussi à trouvé avec un résultat fiable sans boucler sur les dernières lignes de UsedRange, genre:

Function DerLigne(Optional sh As Worksheet = Nothing)
    Dim r As Long
    If sh Is Nothing Then Set sh = ActiveSheet
    With sh.UsedRange
        For r = .Row + .Rows.Count - 1 To .Row Step -1
            If Application.CountA(sh.Rows(r)) <> 0 Then Exit For
        Next
    End With
    DerLigne = r
End Function

A+

 

[david84]

Re : Ramener la dernière ligne non vide de la feuille (ligne filtrée, masquée ou non)

Re
Merci Hasco pour ta proposition qui semble donner d'après mes 1er tests les bons résultats (les mêmes que ceux que j'obtiens avec Evaluate mais peut-être est-ce plus rapide ?).

J'ai jamais réussi à trouvé avec un résultat fiable sans boucler sur les dernières lignes de UsedRange

J'en tire pour l'instant la même conclusion.
Mes recherches sur le Web relèvent ce fait et certains parlent même de bug concernant UsedRange.
Pourtant, quand on lit l'aide d'Excel, il n'est pas dit que UsedRange ramène la plage utile de la feuille (donc de la première à la dernière cellule non vide) mais plutôt

Cette propriété renvoie un objet Range qui représente la plage utilisée dans la feuille de calcul spécifiée

, donc une plage susceptible de contenir des cellules qui contenaient des données qui ont été effacées mais dont la page n'a pas été "rafraîchie".
Si c'est le cas, UsedRange fait donc son travail.
Cela me paraît tout de même bizarre qu'il n'y ait pas de propriété ou fonction VBA qui puisse te ramener la dernière cellule ou ligne non vide d'une feuille de calcul et ce quel que soit le cas de figure (que celle-ci soit apparente, filtrée ou masquée)...
A+

 

[mapomme]

Re : Ramener la dernière ligne non vide de la feuille (ligne filtrée, masquée ou non)

Bonjour à tous,

Pour le fun, si on remplace z par la formule ="" dans la cellule D18 (ligne 18 étant toujours masquée), seule la méthode de Hasco donne le bon résultat(18). Celle de Victor aussi (mais le UsedRange est capricieux).

Si on efface D18 sans démasquer la ligne (on inscrit D18 dans la barre d'adresse/nom, on efface ="" de la zone de formule puis entrée), alors seule la méthode d'Hasco et de David donnent le bon résultat (7).

 

[mapomme]

Re : Ramener la dernière ligne non vide de la feuille (ligne filtrée, masquée ou non)

Bonjour à tous,

Toujours pour le fun, un code (plus long que celui d'Hasco) mais qui donne des résultats identiques.

Sub DerLigneNonVideM()    '-mapomme
Dim ConstFormule, xtype
Dim xrg As Range, xarea As Range
Dim Lmax As Long, L As Long
ConstFormule = Array(xlCellTypeConstants, xlCellTypeFormulas)
  On Error Resume Next
  For Each xtype In ConstFormule
    Set xrg = ActiveSheet.Cells.SpecialCells(xtype, 23)
    If Not xrg Is Nothing Then
      For Each xarea In xrg.Areas
        L = xarea.Row - 1 + xarea.Rows.Count
        If L > Lmax Then Lmax = L
      Next xarea
    End If
  Next xtype
  MsgBox Lmax
End Sub

 

[david84]

Re : Ramener la dernière ligne non vide de la feuille (ligne filtrée, masquée ou non)

Bonjour mapomme,
merci pour ta participation ta proposition et ton fichier test.

Suite à ta remarque justifiée on peut modifier Evaluate comme suit :

DCel = Evaluate("MAX(IF(NOT(ISBLANK(" _
& Feuil1.UsedRange.Address(external:=True) & ")),row(" _
& Feuil1.UsedRange.Address(external:=True) & ")))")

MsgBox DCel

Je regarderai de plus près ta proposition dès que possible.
A+

 

[david84]

Re : Ramener la dernière ligne non vide de la feuille (ligne filtrée, masquée ou non)

Bonjour,

comme promis, un petit retour sur les propositions de Hasco et mapomme qui donnent des résultats corrects et fiables sans passer par Evaluate :
La proposition de Hasco me semble la plus évidente à appréhender : travailler sur le UsedRange de la feuille et vérifier en pas négatif grâce à l'utilisation de CountA si la ligne n'est pas vide.
Il me semble même qu'en remplaçant

If Application.CountA(sh.Rows(r)) <> 0

par

If Application.CountA(.Rows(r)) <> 0

, cela permet de restreindre la plage traitée par CountA à la ligne du UsedRange et non à la ligne entière de la feuille.

La proposition de mapomme est plus complexe mais permet toutefois de ne traiter que les plages non vides, chose qui peut être intéressante si le UsedRange (la zone utilisée de la feuille) est important au regard de la plage contenant les cellules non vides.

Merci à Roger et à youki pour leur proposition.
Si vous avez d'autres propositions, n'hésitez pas.

A+

 

[Guest]

Re : Ramener la dernière ligne non vide de la feuille (ligne filtrée, masquée ou non)

Bonjour,

@David

cela permet de restreindre la plage traitée par CountA à la ligne du UsedRange et non à la ligne entière de la feuille.

:

Sauf que tel que j'ai écris ici cette macro, r renvoie un numéro de ligne absolu par rapport à la feuille et que UsedRange ne commence pas forcément à la ligne 1.

sh.rows(10) et UsedRange.rows(10) ne sont pas forcément les mêmes lignes.

J'avais bien pensé à mettre :

If Application.CountA(Intersect(sh.Rows(r), .Cells)) <> 0 Then

Mais pour ne pas alourdir la démonstration, je ne l'ai pas fait.
Mes plates excuses.

A+++ l'ami David

 

[david84]

Re : Ramener la dernière ligne non vide de la feuille (ligne filtrée, masquée ou non)

Re

Sauf que tel que j'ai écris ici cette macro, r renvoie un numéro de ligne absolu par rapport à la feuille et que UsedRange ne commence pas forcément à la ligne 1.

Oui bien vu l'ami, je n'avais pas pensé à cela...

J'avais bien pensé à mettre :
Code :
If Application.CountA(Intersect(sh.Rows(r), .Cells)) <> 0 Then

Mais pour ne pas alourdir la démonstration, je ne l'ai pas fait.

Effectivement, peut-être que

DerLigneH = r + sh.UsedRange.Row - 1

comme utilisé dans ta boucle le fait également mais à tester plus avant.

Mes plates excuses

J'adore ton humour !
A+