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.

 

[patricktoulon]

une autre sans evaluate qui prend en compte la valorisation "0"

Sub testx()
    Dim t, x, z
    With Application
        x = .Max(.IfError(.Match(0, [A:A], 1), 0), .IfError(.Match("z", [A:A], 1), 0))
    End With
    MsgBox x
End Sub

testé aussi avec un "0" entre 2 lignes pleines
donc on est bon
comme tu peux le constater cette fois ci j'utilise l'argument (1)"le plus proche" avec match

 

[patricktoulon]

alors on est bon ou je cherche encore d'autre combines

 

[Dudu2]

En effet, ça marche très bien et c'est extrêmement rapide (testé en temps d'exécution sur une boucle de 1000 appels).
La seule chose qui me questionne c'est cette limite de "z". On ne peut rien avoir plus grand dans les cellules ?

 

[patricktoulon]

La seule chose qui me questionne c'est cette limite de "z". On ne peut rien avoir plus grand dans les cellules ?

??????????????????

 

[patricktoulon]

quel mot pourrais commencer par une 27eme lettre
n'oublie pas que l'on est en match approximatif par l'argument 1 donc "z" peut être "a" si il y a rien de plus proche du "z"
démonstration

 

[Dudu2]

En fait je ne comprends pas bien cette instruction de .Match, faut que je regarde.
J'ai mis un "}" qui est > "z" ou des caractères de l'espaces tirés de charmap.exe et ça passe.

Sinon, tu peux arrêter de solutionner. Pour l'instant ça passe tous mes tests.
Et c'est une instruction à conserver car ce calcul de la dernière ligne valorisée est tout sauf basique.

Je te remercie pour ton aide précieuse.

 

[patricktoulon]

re

En fait je ne comprends pas bien cette instruction de .Match, faut que je regarde.

en fait c'est tout simple
application.match("z",[A:A],1) c'est carrément un match joker à lui tout seul il te donnera toujours la dernière valeur string et combien même il y aurait un "z" dans une cellule avant la fin


il faut bien faire la différence avec application.match("z",[A:A],0)
démonstration

ce qu'il faut comprendre c'est qu'avec equiv(c'est le match pour formule) on peut utiliser le joker"*" et donc evaluate equiv donne
MsgBox Evaluate("=MATCH(""*"",A:A,-1)")

mais avec le application.match le joker ne peut etre utiliser le joker serait pris comme un caractère recherché
donc vba utilise "z" et l’argument= 1 ="le plus proche"

 

[patricktoulon]

si tu préfère evaluate avec le joker"*" si c'est plus intelligible pour toi ça donne ça

Sub test()
    MsgBox Evaluate("MAX(MATCH(""*"",A:A,-1),MATCH(0,A:A,1))")
End Sub

en formule çà donne ca
= MAX(EQUIV("*";A1:A30;-1);EQUIV(0;A1:A30;1))
tu m'a fait un peu réviser mes bidouilles evaluate

 

[Dudu2]

Intuitivement le joker me plait davantage
x = .Max(.IfError(.Match(0, Columns(1), 1), 0), .IfError(.Match("*", Columns(1), -1), 0))

Mais il faut encore que j'analyse ces fonctions à tête reposée.

 

[patricktoulon]

re
c'est l'argument 3 qui est important pas que le joker

 

[Dudu2]

Bonjour PatrickToulon,
J'espère que tu as bien dormi car il faut continuer à solutionner !
Si la dernière cellule de la colonne est une formule qui résulte en une chaine vide (=""):
- la méthode Joker ne la voit pas.
- la méthode "z" la voit.
Si la dernière cellule de la colonne est une formule qui résulte en un nombre nul (=0):
- la méthode Joker ne la voit pas.
- la méthode "z" ne la voit pas.
(Voir fichier mis à jour du Post #1)

 

[Dudu2]

Quand on pense qu'il ne s'agit "QUE" de trouver la dernière ligne d'un colonne !
Hâtez-vous lentement, et sans perdre courage,
Vingt fois sur le métier remettez votre ouvrage,
Polissez-le sans cesse, et le repolissez,
Ajoutez quelquefois, et souvent effacez.

 

[patricktoulon]

re
Bonjour Dudu2
donc la méthode max (méthode"z" , méthode "0") est bonne

Attention a la façon dont tu t'exprime

Si la dernière cellule de la colonne est une formule qui résulte en un nombre nul (=0):

0 n'est pas un nombre nul . surtout en VBA nul a une tout autre valeur


après pour le "" en retour de formule ,c'est un peu difficile a gérer car "" est rien du tout
parti de là je vois pas comment capter la valeur string empty

 

[Dudu2]

Ok on va en rester là.
La méthode Find("*") trouve bien tout ce qu'il faut mais hélas il n'y a pas moyen de lui dire de prendre en compte les lignes exclues par le(s) filtre(s).
Soit il faut précéder ces instructions d'un:

With <Worksheet>
    If Not .AutoFilter Is Nothing Then .AutoFilter.ShowAllData
End With

mais alors on perd les filtres.
Soit il faut supprimer avant puis restorer après les filtres de la feuilles, ce qui est possible, mais ça devient trop complexe.
Je vais rester sur la méthode triviale:

'----------------------------------------------------------------
'Calcul de la dernière ligne d'une colonne par analyse de contenu
'Les méthodes Cells(Rows.Count, 1).End(xlUp).row et autres Find
'sont sensibles aux filtres et donc à ne pas utiliser sauf à
'retirer les filtres avant de les utiliser:
'With <Worksheet>
'    If Not .AutoFilter Is Nothing Then .AutoFilter.ShowAllData
'End With
'----------------------------------------------------------------
Function NbLignesEnColonne(ByVal Feuille As Worksheet, ByVal NuméroColonne As Long) As Long
    Dim Rng As Range
    Dim TabValues() As Variant
    Dim i As Long
  
    Set Rng = Intersect(Feuille.UsedRange, Feuille.Columns(NuméroColonne))
  
    If Not Rng Is Nothing Then
        TabValues = Rng.Value
      
        For i = UBound(TabValues, 1) To 1 Step -1
            If Not VarType(TabValues(i, 1)) = vbEmpty Then Exit For
        Next i
    End If
  
    NbLignesEnColonne = i
End Function

Merci encore pour les recherches.

 

[patricktoulon]

tien j'ai retrouvé une de mes bidouilles
usedrange on range def

'************************************************************************************
'              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
    With tableau
        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)
    End With
End Function
'sub de test
Sub testusedRangedef()
    Dim plage As Range
    Set plage = [A1:A6000]
    MsgBox UsedrangeOnRangeDef(plage).Address
End Sub

j'ai testé elle détecte bien la valeur vide string retournée par une formule