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

AlainProviste

XLDnaute Nouveau
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.
 
Solution
Re, Bonjour Eriiic,
Si une petite fonction perso ne vous rebute pas, c'est quand même plus simple :
VB:
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

XLDnaute Barbatruc
Supporter XLD
Bonjour Alain,
En PJ un essai avec :
VB:
=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.
 

Pièces jointes

  • Alain.xlsm
    9.5 KB · Affichages: 19

sylvanu

XLDnaute Barbatruc
Supporter XLD
Re, Bonjour Eriiic,
Si une petite fonction perso ne vous rebute pas, c'est quand même plus simple :
VB:
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
 

Pièces jointes

  • Alain2.xlsm
    61.5 KB · Affichages: 5

sylvanu

XLDnaute Barbatruc
Supporter XLD
Avec Pos=0, c'est à dire syntaxe =LastValue(0) vous avez le nombre complet. Au cas où.
VB:
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
 

Pièces jointes

  • Alain3.xlsm
    61.7 KB · Affichages: 2

sylvanu

XLDnaute Barbatruc
Supporter XLD
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

XLDnaute Barbatruc
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 :
VB:
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+
 

Pièces jointes

  • Derlig(1).xlsm
    66.9 KB · Affichages: 2

job75

XLDnaute Barbatruc
Avec un argument optionnel la même fonction permet de renvoyer le numéro de ligne :
VB:
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).
 

Pièces jointes

  • Derlig(2).xlsm
    67.6 KB · Affichages: 2

AlainProviste

XLDnaute Nouveau
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

XLDnaute Barbatruc
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
VB:
MsgBox Evaluate("MAX(ROW(A1:E2500)*(A1:E2500>0))")

vous voulez le concact des 5 colonne le ligne !!?
ben c'est simple
VB:
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
VB:
MsgBox Join(Application.Index(Feuil1.[A1:E2500].Value, Evaluate("MAX(ROW(feuil1!A1:E2500)*(feuil1!A1:E2500>0))"), [{1, 2, 3, 4, 5}]))
 
Dernière édition:

Discussions similaires

Réponses
7
Affichages
475

Statistiques des forums

Discussions
313 194
Messages
2 096 094
Membres
106 495
dernier inscrit
dylan.44