Autres Dernière ligne d'une plage comportant au moins 1 valeur supérieure à zéro

[AlainProviste]

Bonjour,

malgré toutes les formules que j'ai passées en revue et testées, je n'en ai trouvées aucune qui répond à mon attente particulière : je m'explique,

Je souhaite extraire la dernière ligne d'une plage de 6 colonnes sur 2500 Lignes qui contient au minimum une valeur supérieure à zéro.

A B C D E F G H I J K L
0 0 1 4 3 0 0 0 1 3 4 6
1 0 7 0 1 0
0 0 0 0 1 3
0 0 0 1 0 0
0 0 0 0 0 0
3 1 0 0 0 5
0 0 1 3 4 6
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0 etc c'est à dire 000000 jusqu'à 2500 ème ligne...
Donc ici en colonnes G1 H1 I1 J1 K1 L1 il faudrait que la valeur 0 0 1 3 4 6 soit affichée ?
Toutes les formules qui se rapprochent de mon problème me donnent : 3 1 1 3 4 6 , ce n'est pas ce que je recherche.

En espérant avoir été clair et qu'une formule "magique" puisse m'être proposée.
Par avance merci.

 

[sylvanu]

Bonjour Alain,
En PJ un essai avec :

=INDEX(A:A;MAX(
MAX(SI(($A2:$A44)=0;0;LIGNE($A2:$A44)));
MAX(SI(($B2:$B44)=0;0;LIGNE($B2:$B44)));
MAX(SI(($C2:$C44)=0;0;LIGNE($C2:$C44)));
MAX(SI(($D2:$D44)=0;0;LIGNE($D2:$D44)));
MAX(SI(($E2:$E44)=0;0;LIGNE($E2:$E44)));
MAX(SI(($F2:$F44)=0;0;LIGNE($F2:$F44)))
))

En matriciel, à valider par CTRL+MAJ+Entrée.
Avec 2500 lignes je pense que ça va être long.

Peut être une fonction perso VBA serait plus rapide.

 

[eriiic]

Bonjour,

si tu n'es pas à 1 colonne près (que tu peux masquer).
eric

Edit, salut sylvanu, c'est en partie pour ça que je n'ai pas trop osé une matricielle

 

[AlainProviste]

Bonjour Sylvanu,

j'opterais plutôt pour la macro car en effet avec une matricielle cela risque de faire long...!
Cependant j'ai ouvert le fichier joint mais le code de la macro n'y figure pas ?

 

[sylvanu]

Re, Bonjour Eriiic,
Si une petite fonction perso ne vous rebute pas, c'est quand même plus simple :

Function LastValue(Pos)
Dim T, DerLig As Integer, i As Integer
LastValue = ""
DerLig = Range("A65500").End(xlUp).Row
T = Range("A2:F" & DerLig)
For i = DerLig - 1 To 1 Step -1
    If T(i, 1) > 0 Or T(i, 2) > 0 Or T(i, 3) > 0 Or T(i, 4) > 0 Or T(i, 5) > 0 Or T(i, 6) > 0 Then
        LastValue = T(i, Pos)
        Exit Function
    End If
Next i
End Function

 

[sylvanu]

Le premier essai était par formule.

En matriciel, à valider par CTRL+MAJ+Entrée.

Dans le post #5 c'est par fonction.
A vous de l'adapter suivant les colonnes à considerer.

 

[sylvanu]

Avec Pos=0, c'est à dire syntaxe =LastValue(0) vous avez le nombre complet. Au cas où.

Function LastValue(Pos)
Dim T, DerLig As Integer, i As Integer
LastValue = ""
DerLig = Range("A65500").End(xlUp).Row
T = Range("A2:F" & DerLig)
For i = DerLig - 1 To 1 Step -1
    If T(i, 1) > 0 Or T(i, 2) > 0 Or T(i, 3) > 0 Or T(i, 4) > 0 Or T(i, 5) > 0 Or T(i, 6) > 0 Then
        If Pos > 0 Then
            LastValue = T(i, Pos)
            Exit Function
        Else
            LastValue = Format(Val(T(i, 1) & T(i, 2) & T(i, 3) & T(i, 4) & T(i, 5) & T(i, 6)), "000000")
            Exit Function
        End If
    End If
Next i
End Function

 

[AlainProviste]

Waouuu !
Excellent ...
Le fichier Alain2.xlsm de Sylvanu fonctionne à merveille. !

Merci beaucoup à vous deux pour votre aide,
S'agissant de Alain3.xlsm, il ne m'est pas vraiment utile mais je le garde sous le coude.

Cordialement
Alain

 

[sylvanu]

Bonjour Alain,
Je me suis aperçu que la macro fournie ne se recalculait pas en automatique et qu'il fallait revalider les formules pour les mettre à jour.
En PJ c'est rectifié avec : Application.volatile.

 

[eriiic]

Bonjour,

Sylvanu, éventuellement tu peux lui passer la plage concernée en paramètre pour éviter le recalcul systématique et éviter des latences dues au Volatile non ?
eric

 

[sylvanu]

Bonjour,
C'était une possibilité. Cependant cela obligait à aller chercher la dernière ligne occupée, et quand on a 2500 lignes, ça m'a semblé plus simple de passer par une recherche automatique.
D'autant que sur mon vieux PC un Recalculate ne prend que 45ms, rien de rédhibitoire, et la formule est simplissime.

 

[job75]

Bonjour AlainProviste, sylvanu, eriiiic, le forum,

En PJ c'est rectifié avec : Application.volatile.

Quand c'est possible comme ici il faut absolument éviter les fonctions volatiles car elles se recalculent chaque fois qu'on modifie une cellule quelconque.

Voyez le fichier joint et cette fonction VBA matricielle :

Function Derlig(colonnes As Range)
Dim nlig&, ncol%, tablo, a(), i&, j%, k%
nlig = colonnes(colonnes.Rows.Count, 1).End(xlUp).Row
ncol = colonnes.Columns.Count
tablo = colonnes.Resize(nlig, ncol) 'matrice, plus rapide
ReDim a(1 To ncol)
For i = nlig To 1 Step -1
    For j = 1 To ncol
        If tablo(i, j) > 0 Then
            For k = 1 To ncol: a(k) = tablo(i, k): Next k
            Derlig = a 'vecteur horizontal
            Exit Function
        End If
Next j, i
End Function

Formule entrée en bloc sur toute la plage I4:N4 et validée par Ctrl+Maj+Entrée =Derlig(A:F)

Comme elle n'est calculée qu'une seule fois elle est 6 fois plus rapide.

A+

 

[job75]

Avec un argument optionnel la même fonction permet de renvoyer le numéro de ligne :

Function Derlig(colonnes As Range, Optional ligne As Boolean)
Dim nlig&, ncol%, tablo, a(), i&, j%, k%
nlig = colonnes(colonnes.Rows.Count, 1).End(xlUp).Row
ncol = colonnes.Columns.Count
tablo = colonnes.Resize(nlig, ncol) 'matrice, plus rapide
ReDim a(1 To ncol)
For i = nlig To 1 Step -1
    For j = 1 To ncol
        If tablo(i, j) > 0 Then
            If ligne Then Derlig = i: Exit Function
            For k = 1 To ncol: a(k) = tablo(i, k): Next k
            Derlig = a 'vecteur horizontal
            Exit Function
        End If
Next j, i
End Function

Fichier (2).

 

[AlainProviste]

Bonjour Alain,
Je me suis aperçu que la macro fournie ne se recalculait pas en automatique et qu'il fallait revalider les formules pour les mettre à jour.
En PJ c'est rectifié avec : Application.volatile.

Bonjour Silvanu, désolé de ma réponse tardive mais j'étais absent.
Merci d'avoir affiné ta macro cependant dans le cas que tu présentes (Alain4.xlsm), l'hypothèse d'une série de valeurs sur 1 ligne/ 6 colonnes, toutes à "zéro", ne se présentera jamais dans mon projet.
De ce fait je suis tenu de procéder à un nouveau calcul chaque fois car une nouvelle série avec des nombres supérieurs à zéro, dans au moins une des 6 colonnes, s'incrémentera.

Il n'en demeure pas moins que je remercie également Eriiic et Job75 pour leurs interventions non moins pertinentes...
Cordialement

 

[patricktoulon]

bonsoir a tous
et ben dit donc
vous en faite tout un ramdam pour un max row
restons simple ( a valider par CTRL+MAJ+ENTER)

=MAX(LIGNE(A1:E2500)*(A1:E2500>0))

le vba vous y tenez ??!!??
ok

MsgBox Evaluate("MAX(ROW(A1:E2500)*(A1:E2500>0))")

vous voulez le concact des 5 colonne le ligne !!?
ben c'est simple

MsgBox Join(Application.Index(Feuil1.[A1:E2500].Value, Evaluate("MAX(ROW(feuil1!A1:E30)*(feuil1!A1:E30>0))"), Array(1, 2, 3, 4, 5)))

ou même écrite comme çà pour l'array

MsgBox Join(Application.Index(Feuil1.[A1:E2500].Value, Evaluate("MAX(ROW(feuil1!A1:E2500)*(feuil1!A1:E2500>0))"), [{1, 2, 3, 4, 5}]))