XL 2016 VBA: dernière ligne valorisée d'une colonne ?

[Dudu2]

Bonjour à tous,

Une question apparemment simple: quel est le numéro de la dernière ligne valorisée (formule ou constante) d'une colonne ?

Je pense que beaucoup d'entre nous répondraient un truc du genre:
DerniereLigneUtilisée = Range("X" & Rows.Count).End(xlUp).Row 'où X est la colonne donnée
ou encore:
DerniereLigneUtilisée = ActiveSheet.Columns(X).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

SAUF que ça ne marche pas toujours quand par exemple il y a un filtre actif sur la colonne et que la dernière ligne réelle est masquée par le filtre (voir fichier joint).
Donc la question est, à part la méthode "artisanale" employée dans le fichier joint, y a-t-il un autre moyen de connaître cette ligne ?

Merci par avance.

Comparatif des méthodes qui fonctionnent mis à jour en tenant compte des plus récents posts.

 

[Dudu2]

Les Find("*") ignorent les lignes filtrées. Toujours le même problème.

 

[patricktoulon]

tiens dis moi tu sais comment récupérer le criterial1 de autofilter
j'ai une idée que je voudrais tester avant de partir

 

[Dudu2]

J'ai ça:

 With WS_Filtres.AutoFilter
        ZoneFiltres = .Range.Address
        With .Filters
            ReDim TabFiltres(1 To .Count, 1 To 3)
            For i = 1 To .Count
                With .Item(i)
                    If .On Then
                        TabFiltres(i, 1) = .Criteria1
                        If .Operator Then
                            TabFiltres(i, 2) = .Operator
                            TabFiltres(i, 3) = .Criteria2
                        End If
                    End If
                End With
            Next
        End With

Attention car il faut gérer tous les filtres de la feuille, pas seulement celui de la colonne concernée s'il y en a un.

 

[patricktoulon]

tiens j'ai essayé de faire quelque chose mais je n'y suis pas arrivé
le but étant de chopper le critère du filtre, l’arrêter, de faire le find et de remettre le filtre

'************************************************************************************
'              FONCTION FIND(LAST) ROW,COLUMN,CELL ON <<<defined range>>>
'Autor:patricktoulon
'version beta
'date version 29/03/2020
'************************************************************************************
Function UsedrangeOnRangeDef(tableau) As Range
    Dim lig&, col&, cel1 As Range, cel2 As Range, truc$
    With tableau
        With .Parent.AutoFilter
            If .Range.Address = tableau.Address Then
                'ici on recupere le critere du filtre
                For i = 1 To .Filters.Count
                    If .Filters.Item(i).On Then truc = .Filters.Item(i).Criteria1
                Next
                MsgBox truc    'juste pour voir
                If Not .Parent.AutoFilter Is Nothing Then .Parent.AutoFilter.ShowAllData    'ici on enleve le filtre
            End If
        End With

        Set cel1 = .Cells(1)
        col = .Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        lig = .Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set cel2 = Cells(lig, col)
        Set UsedrangeOnRangeDef = .Parent.Range(cel1, cel2)
        .AutoFilter Field:=1, Criteria1:=truc    'ICI ON REMET LE FILTRE COMME IL ETAIT
    End With
End Function

'sub de test

Sub testusedRangedef()
    Dim plage As Range
    Set plage = [A1:A6000]
    MsgBox UsedrangeOnRangeDef(plage).Address
End Sub

 

[patricktoulon]

je reviens pas avant ce soir j’espère que tu va trouver çà m’intéresse

 

[Dudu2]

Comme je l'ai indiqué au Post #19, d'une part le Find("*") permet bien de tout trouver sauf qu'il est sensible au filtrage des lignes. Dé-filtrer toute la feuille avant et re-filtrer après est possible mais je trouve que ça fait beaucoup de manips juste pour un numéro de dernière ligne.

J'avais récupéré puis emballé un code qui fait ça. Je l'ai mis dans ce fichier. Pour une recherche de dernière ligne il faudrait concentrer l'affaire en 1 seule fonction, ici séparée en 2 étapes.
Edit: Cette méthode est très consommatrice de ressources. Le temps d'exécution est 300 fois supérieur à la méthode du scan des contenus du Post #29.

Au final je vais rester sur le code du Post #29 pour les applis qui me sont demandées, le coût CPU est finalement très limité pour des feuilles 'standards" et dépend surtout de la différence de hauteur entre le UsedRange et la colonne testée.
Une différence de 1.048.575 (cas d'un fichier extrême ou bien pourri) se résout en 15/100ème de seconde sur ma vieille CPU (ce qui est très long en terme de CPU).
Une différence de quelques 100 lignes se résout en 1/10.000ème de seconde. Pas de quoi se prendre la tête.

 

[pierrejean]

Re

Un essai

Sub test()
tablo = ActiveSheet.UsedRange
For n = UBound(tablo, 1) + 1 To LBound(tablo, 1) Step -1
   If Range("A" & n).Formula <> "" Then
      nb = n
      Exit For
   End If
Next
MsgBox (nb)
End Sub

 

[Dudu2]

Bonjour pierrejean,
Comme ça, ça fonctionne aussi.
Quelques remarques sur ce code si tu permets.

Tu ne déclares pas tes variables et c'est un choix, mais je pense que c'est mieux de le faire

Je ne comprends pas pourquoi tu commences la boucle à UBound(tablo, 1) + 1.
De plus Nb est inutile car n contient la bonne valeur.

Tu charges en tablo tout le UsedRange. L'intersection du UsedRange avec la colonne concernée suffit et peut économiser une grande quantité de mémoire + chargement.

Le plus important peut-être, est que tu as un tableau chargé des valeurs (je pense car non précisé) que tu n'exploites pas car tu testes sur les cellules. Or tester le tableau va 10 fois plus vite que tester les cellules.
Pour tester le tableau tu as 2 moyens:
Dim tablo() as Variant
tablo = <Range>.Formula et If Len(tablo(i, 1)) Then Exit For
tablo = <Range>.Value et If Not VarType(tablo(i, 1)) = vbEmpty Then Exit For
Dans le code du Post #29 j'ai utilisé la 2ème méthode car elle est plus représentative d'un test IsEmpty(Cellule).

 

[pierrejean]

Re
Merci .Bien noté

 

[patricktoulon]

bonsoir a tous je viens de rentrer
bon alors dans tout çà ???
a t on une fonction sans faille ,qui fait le larron ou non ?

 

[Dudu2]

Bonsoir PatrickToulon,
Nada, y a rien qui marche en standard. Soit faut dé-filtrer et utiliser les trucs classiques genre .End(xlUp) ou Find("*") dont tout le monde pense qu'ils marchent tout le temps, soit faut faire du code comme par exemple celui que j'ai indiqué dans le Post #29 et que j'utilise dorénavant pour mon usage personnel.

Le fichier mis à jour du Post #1 contient toutes les options de ce fil.

 

[patricktoulon]

Bonsoir dudu2
Ah.... on trouvera va t'inquiet quand il faut sortir des sentiers battus je suis pas le dernier
je suis persuadé que excel garde tout cela en mémoire quelque part et j'ai ma petite idée sur la question

 

[Dudu2]

Tiens, en regardant les méthodes de l'objet Range j'ai trouvé un truc qui peut être exploité:

'---------------------------------------------------------------
'Calcul de la dernière ligne d'une colonne par ColumnDifferences
'---------------------------------------------------------------
Function NbLignesEnColonneDifférences(ByVal Feuille As Worksheet, ByVal NuméroColonne As Long) As Long
    Dim Addr As String
 
    If IsEmpty(Feuille.Cells(Rows.Count, NuméroColonne)) Then
        On Error Resume Next
        Addr = Feuille.Columns(NuméroColonne).ColumnDifferences(Feuille.Cells(Rows.Count, NuméroColonne)).Address
        If Err.Number = 0 Then NbLignesEnColonneDifférences = CLng(Mid(Addr, InStrRev(Addr, "$") + 1))
        On Error GoTo 0
    Else
        NbLignesEnColonneDifférences = Rows.Count
    End If
End Function

Edit: c'est 10 fois moins performant que le scan de contenu mais ça reste efficace

 

[mapomme]

Bonsoir,

Tirée de lointains souvenirs, une fonction personnalisée :

Function NumDerLig&(plage As Range, Optional relatif)
   NumDerLig = Application.Max(Application.IfError(Application.Match("z", plage.Columns(1)), 0), _
   Application.IfError(Application.Match(9 ^ 99, plage.Columns(1)), 0))
   If NumDerLig > 0 Then If IsMissing(relatif) Then NumDerLig = plage.Row - 1 + NumDerLig
End Function

 

[Dudu2]

Bonsoir mapomme,

En fait, même sans fonction, en écriture directe sur une colonne (ici la 1ère de la feuille active):

With Application
    NbLignes = .Max(.IfError(.Match("z", ActiveSheet.Columns(1)), 0), .IfError(.Match(9^99, ActiveSheet.Columns(1)), 0))
End With

Et traduisible en formule:
=MAX(SIERREUR(EQUIV("z"; A:A;1);0); SIERREUR(EQUIV(9^99; A:A;1);0))

Ça me semble LA solution ! (En plus je l'avais en formule dans mon historique )