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

Dudu2 · 6 Août 2020

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.

mapomme · 8 Août 2020

Bonsoir,

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

VB:

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

patricktoulon · 27 Janvier 2021

re
en même temps y a qu' zouzoukistan que l'on aura à chercher "zzza"
je retiens le nombre plus grand

VB:

Sub test2()
With Application: NumDerLig = .Max(.IfError(.Match("zzz", [A:A], 1), 0), .IfError(.Match(1299 ^ 99, [A:A], 1), 0)): End With
MsgBox NumDerLig
End Sub

au delà si c'est pour avoir un code indigeste je rejoins plutôt @job75 et trouver une autre solution

mais ca reste un mauvais choix car une formule renvoyant un vide est valorisée donc méthode pas bonne puisque finalement c'est le sujet du topic
la preuve en image

désolé mon @Dudu2 il faut retravailler la question

Dranreb · 27 Janvier 2021

Bonsoir

Dudu2 à dit:
j'ai essayé de mettre le plus grand possible (limites Excel) mais il je n'y arrive pas

C'est (2 ^ 53 - 1) * 2 ^ 971
Et c'est une limite machine, non Excel.

patricktoulon · 27 Janvier 2021

@Dudu2
tiens j'ai fouillé dans mes bricolage "evaluate"
et effectivement pour le texte la formule est plus puissante et ne prend pas en compte les formules renvoyant un vide
!!!!!et le fait d'utiliser"*" avec "-1" la rend récursive et donne donc bien le dernier texte!!!!!

VB:

Sub testx()
With Application
MsgBox Evaluate("IFERROR(MATCH(""*"",A1:A100,-1),0)") 'ligne max pour texte(zappe les formule renvoyant un vide)
MsgBox Evaluate("MAX(ROW(A1:A100)*(A1:A100>0)*(A1:A100<>""""))") 'ligne max numérique plus lourde et longue en terme de duré quand la plage est grande
'le test max des des 2
MsgBox "les deux" & vbCrLf & .Max(Evaluate("IFERROR(MATCH(""*"",A1:A100,-1),0)"), Evaluate("MAX(ROW(A1:A100)*(A1:A100>0)*(A1:A100<>""""))"))
End With
End Sub

tester avec formules renvoyant du vide

edit on en fait une petite fonction sympa

un seul petit detail :la valorisation "0" dans la cellule ne match pas

VB:

Sub testx()
MsgBox LastCellValuedInColumn([A1:A100])
End Sub

Function LastCellValuedInColumn(colonne As Range)
Dim c$
c = colonne.Address(0, 0)
With Application
LastCellValuedInColumn = .Max(Evaluate("IFERROR(MATCH(""*""," & c & ",-1),0)"), Evaluate("MAX(ROW(" & c & ")*(" & c & ">0)*(" & c & "<>""""))"))
End With
End Function

et puis soyons fous!! ; finalement avec max row on peut tout avoir
avec celle ci même la valorisation"0" match

VB:

Sub testy()
MsgBox LastCellValuedInColumnX([A1:A100])
End Sub


Function LastCellValuedInColumnX(colonne As Range)
c = colonne.Address(0, 0)
LastCellValuedInColumnX = Evaluate("MAX(ROW(" & c & ")*(" & c & "<>""""))")
End Function

là pour le coup c'est comme chez renault

là oui ça marche
on a pas de soucis de la grandeur du nombre
on a pas le soucis des formules renvoyant du vide
on a pas le soucis de"zzz......."
j'ai testé on a bien la dernière cellule valorisée (formule ou pas) même si c'est zero

Dudu2 · 28 Janvier 2021

Bonjour @patricktoulon, @mapomme, @job75,
Bien vu pour cette formule matricielle en evaluate.
La question de savoir s'il faut compter ou pas les valeurs vides est ouverte. Perso, je pense qu'il faut les compter dans la recherche de la dernière ligne. Et pourquoi pas avoir une fonction différente pour la recherche de la dernière ligne valorisée. Pour la dernière ligne (valorisée ou pas), la formule de @patricktoulon serait:

VB:

LastCellValuedInColumnX = Evaluate("MAX(ROW(" & c & ")*(ISTEXT(" & c & ")))")

Sur un UsedRange de 100.000 lignes c'est 5 secondes pour 100 exécutions (sur ma CPU). C'est pas très performant mais ça marche et pour 1 recherche on n'est pas à 5/100ème de seconde près !

Je l'ai ajoutée dans mon comparatif.

Dudu2 · 28 Janvier 2021

@Dranreb

C'est (2 ^ 53 - 1) * 2 ^ 971

Malgré le "+ 1" d2 ne va pas au-delà de la valeur de d1 ce qui montre que c'est une limite au moins Excel.
Par contre c'est quand même étrange qu'on n'ait pas un dépassement de capacité sur d2 = d1 + 1.

Code:

Sub a()
    Dim d1 As Double
    Dim d2 As Double
    
    d1 = (2 ^ 53 - 1) * 2 ^ 971
    'd2 = d1 + 10 ^ 291
    d2 = d1 + 1
    
    [A1].Value = d1
    [A2].Value = d2
    
    MsgBox "d1 = " & d1 & vbCrLf & _
           "d2 = " & d2 & vbCrLf & _
           "d1 " & IIf(d1 > d2, ">", IIf(d1 < d2, "<", "=")) & " d2"
    
    MsgBox "d1 = " & d1 & vbCrLf & _
           "A1 = " & [A1].Value & vbCrLf & _
           "d1 " & IIf(d1 > [A1].Value, ">", IIf(d1 < [A1].Value, "<", "=")) & " A1"
          
    MsgBox "A1 = " & [A1].Value & vbCrLf & _
           "A2 = " & [A2].Value & vbCrLf & _
           "A1 " & IIf([A1].Value > [A2].Value, ">", IIf([A1].Value < [A2].Value, "<", "=")) & " A2"
End Sub

Dranreb · 28 Janvier 2021

C'est parce que cela ne saurait changer sa valeur !
Il ne faut pas oublier qu'à cet ordre de grandeur le bit du poids le plus faible de la mantisse vaut 2 ^ 971
C'est une limite de la représentation des nombres en virgule flottante double précision, quel que soit le programme qui s'en sert.
Des informations en commentaires de cellules dans le classeur joint

Dudu2 · 28 Janvier 2021

OK merci pour l'explication documentée.

Donc pour le Match j'ai fini par faire ça:

VB:

'----------------------------------------------------------------------
'Calcul de la dernière ligne non vide d'une colonne avec fonction Match
'DernièreLigne = DernièreLigneEnColonne(ActiveSheet.Columns(1))
'----------------------------------------------------------------------
Function DernièreLigneEnColonne(ByVal Colonne As Range) As Long
    Const ChaineMax as String = "zzzzzzzzzzzzzzzzzzzz"
    Const NombreMax as Double = (2 ^ 53 - 1) * 2 ^ 971

    If Not Colonne Is Nothing Then
        With Application
            DernièreLigneEnColonne = .Max(.IfError(.Match(ChaineMax, Colonne.Columns(1), 1), 0), _
                                          .IfError(.Match(NombreMax, Colonne.Columns(1), 1), 0))
        End With
    End If
End Function

patricktoulon · 29 Janvier 2021

bonjour @Dudu2

La question de savoir s'il faut compter ou pas les valeurs vides est ouverte. Perso, je pense qu'il faut les compter dans la recherche de la dernière ligne.

ma fois
le titre c'est VBA: dernière ligne valorisée d'une colonne ?
"
si je m'en tiens à

https://www.larousse.fr/dictionnaires/francais/valoriser/81002

alors seul mon exemple simple répond a cette question
car pour moi une cellule avec formule ayant un résultat vide n'est pas valorisée

VB:

Sub testy()
MsgBox LastCellValuedInColumnX([A1:A100])
End Sub


Function LastCellValuedInColumnX(colonne As Range)
c = colonne.Address(0, 0)
LastCellValuedInColumnX = Evaluate("MAX(ROW(" & c & ")*(" & c & "<>""""))")
End Function

sinon le titre aurait du être VBA: dernière cellule utilisée d'une colonne( formule ou pas) résultat ou pas)
c'est pas la même chose

donc histoire de contenter tout le monde

VB:

Sub test()
    MsgBox LatCellUsedInColumn([A1:A100])
    MsgBox LatCellUsedInColumn([A1:A100], True)
End Sub

Function LatCellUsedInColumn(ByVal Colonne As Range, Optional IsValued As Boolean = False) As Long
   Dim C$
    Const ChaineMax As String = "zzzzzzzzzzzzzzzzzzzz"
    Const NombreMax As Double = (2 ^ 53 - 1) * 2 ^ 971
    If Not Colonne Is Nothing Then
        With Application
            If Not IsValued Then
                LatCellUsedInColumn = .Max(.IfError(.Match(ChaineMax, Colonne.Columns(1), 1), 0), .IfError(.Match(NombreMax, Colonne.Columns(1), 1), 0))
            Else
                c = Colonne.Address(0, 0)
                LatCellUsedInColumn = Evaluate("MAX(ROW(" & c & ")*(" & c & "<>""""))")
            End If
        End With
    End If
End Function

UTILISATION:
exemple selon besoins

quand il s'agit de copier une plage valide (dûment remplie d'un tableau) c'est la mienne
quand il s'agit décrire a la suite( du tableau) c'est la tienne (pour ne pas bousiller les formules)

voila tout le monde est content

mapomme · 29 Janvier 2021

Bonsoir à tous,

patricktoulon à dit:
si je m'en tiens à
https://www.larousse.fr/dictionnaires/francais/valoriser/81002

Franchement, la définition du Larousse n'éclaire en rien le débat. La question n'est pas de savoir si la chaine vide est une valeur ou non. C'est juste une convention de langage et encore dans un contexte bien défini.

Je préfère ta deuxième version qui tient compte de l'intention (ou du besoin) de l'utilisateur. A-t-il besoin de tenir compte ou non de la valeur chaine vide ? Si oui, il utilisera une méthode et si non il en utilisera une autre. L'important est qu'il sache ce qu'il veut faire et qu'il utilise une méthode qui lui permettra d'atteindre son but.
Cette discussion lui fournit les outils (dont ta fonction personnalisée) pour faire ce qu'il veut en fonction de ce qu'il désire.

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

Dudu2

XLDnaute Barbatruc

Pièces jointes

mapomme

patricktoulon

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Dudu2

XLDnaute Barbatruc

Dudu2

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Pièces jointes

Dudu2

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Discussions similaires