XL 2010 Petite colle sur cherche dernière ligne vide contenant des formules

[herve62]

Bonjour
Je devais utiliser pour une boucle un For de 1 ou 2 jusqu'à la dernière ligne de données
Ce que je n'avais pas vu au début c'est qu'il y avait une formule dans la colonne de test donc le End(xlup) ne fonctionne pas
Il y a qq années j'avais rencontré ce Pb et j'avais pu trouver un remède via une Fonction
Mais là encore je comprends pas , avec le fichier d'origine (DL Formule) cela ne va pas , voir Sub testDL
J'en ai donc créé un autre (der ligne) avec qq lignes copier/coller du 1er et là ça fonctionne !
Je vois pas ???

 

[soan]

@Hervé, Dudu2, (salut Dranreb, job75)

merci pour ta précision, et j'suis entièrement d'accord avec toi : en effet, ton cadre avait bien raison ! on apprend de ses erreurs, et c'est de la même façon qu'un enfant apprend à marcher : il tombe, puis se relève, et il continue à marcher ; je dirais même que ton cadre de la grosse entreprise ERICSON est un grand sage, car qui peut se vanter de ne jamais faire d'erreurs ? comme a dit JC en son temps : « que celui qui n'a jamais péché lui lance la 1ère pierre ! » 😜

j'espère que tu n'as pas pris pour toi ce court extrait de mon texte qui est dans ta citation : « niveau d'incompétence = un amas de conneries » ; car même si la 2ème partie est dans ta signature, je ne te visais absolument pas : j'ai écrit ça uniquement pour plaisanter par rapport à la fabuleuse découverte de Peter : son génial « principe de Peter » qui explique pourquoi tout ne marche pas aussi bien qu'on aurait pu l'espérer ! ses exemples concrets et réalistes sont une démonstration évidente des travers de la hiérarchie !

il y a même un contre-exemple qui semble montrer une faille dans son principe, mais Peter démontre brillamment que ça semble seulement être une faille, et qu'en réalité ça ne fait que confirmer son principe ! j'avais lu le livre y'a très longtemps, mais j'crois bien que cette fausse faille était nommée : « la sublimation du Principe de Peter » ; pour ceux qui seraient intéressés, voici le livre :​

soan

 

[job75]

Ceci est mieux :

Function DerLigne(xrg As Range) As Long
Set xrg = xrg(1) 'si plus d'une cellule
Do
    If Trim(CStr(xrg)) <> "" Then DerLigne = xrg.Row: Exit Function
    If xrg.Row = 1 Then Exit Function Else Set xrg = xrg(0) 'cellule au dessus
Loop
End Function

Bonne nuit.

 

[herve62]

Bonjour Job , Soan, Dranreb,dudu
Bien merci à toi JOB .... ça c'est du concret ! , cela fonctionne très bien et simple ...pour une fois 😉
Je mets cette fonction bien de côté , la 1ère est bien aussi
Comme le traitement est sur 3000 lignes je vais regarder le temps d'éxé

PS: Non Soan je ne suis pas frustré ! c'était plutôt sur la 1ère 'agression' !! de ma signature .....tout à fait hors sujet !! et qui plus est , incomprise

 

[job75]

Bonjour herve62, le forum,

Comme le traitement est sur 3000 lignes je vais regarder le temps d'éxé

S'il n'y a qu'une formule remonter 3000 lignes c'est peanuts.

A+

 

[herve62]

Là ça met 12sec ?

 

[job75]

Là ça met 12sec ?

Bah il faut savoir mesurer les durées correctement.

Chez moi avec 30 000 lignes le calcul se fait en 0,11 seconde :

Sub testDL()
Dim t, DL&
t = Timer
DL = DerLigne(Sheets("LICA A1").Range("E30000"))
MsgBox "Last row = " & DL & " - calcuk en " & Format(Timer - t, "0.00 \sec")
End Sub

Function DerLigne(xrg As Range) As Long
Set xrg = xrg(1) 'si plus d'une cellule
Do
    If Trim(CStr(xrg)) <> "" Then DerLigne = xrg.Row: Exit Function
    If xrg.Row = 1 Then Exit Function Else Set xrg = xrg(0) 'cellule au dessus
Loop
End Function

 

[job75]

On peut aller plus vite en évaluant en VBA une formule matricielle :

Function DerLigne(xrg As Range)
Set xrg = xrg(1).EntireColumn.Resize(xrg.Row) 'plage
DerLigne = Application.Match(1, Evaluate("IF(TRIM(" & xrg.Address & ")<>"""",1,"""")"))
If IsError(DerLigne) Then DerLigne = 0
End Function

0,03 seconde sur 30 000 lignes.

 

[herve62]

En fait moi je parle du traitement total , j'ai 4 passes > time=12,7 sec
mais bon ce n'est pas 0,03 sec qui font ...........

 

[job75]

Le VBA n'est pas nécessaire avec cette formule matricielle en H1 :

=SIERREUR(EQUIV(1;SI(SUPPRESPACE(E1:E30000)<>"";1;""));0)

A valider par Ctrl+Maj+Entrée.