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.

 

[Dudu2]

Le fichier mis à jour du Post #1 contient toutes les options et codes de ce fil avec notation.

 

[patricktoulon]

bonjour a tous
oui moi aussi je l'ai testé hier la formule 9^9
bref ça ressemble beaucoup a mon idée de départ
j’étais sur que c’était cette voie qu'il fallait prendre
du coup maintenant on a une fonction stable ,propre et pérenne

sous les deux formes vu que perso je préfère evaluate

Sub test()
MsgBox LastValueOnRange([A:A])
End Sub
Function LastValueOnRange(rng As Range)  'par vba application
With Application:    LastValueOnRange = .Max(.IfError(.Match("z", rng), 0), .IfError(.Match(9 ^ 99, rng), 0)): End With
End Function

'================================================================================================
Sub test2()
MsgBox LastValueOnRange2([A:A])
End Sub

Function LastValueOnRange2(rng) ' par evaluate
LastValueOnRange2 = Evaluate("MAX(iferror(match(""z""," & rng.Address(0, 0) & ",1),0), iferror(match(9^99," & rng.Address(0, 0) & ",1),0))")
End Function

 

[mapomme]

(En plus je l'avais en formule dans mon historique )

C'est pas grave. En ces temps de disette xldienne, on se jette sur tout ce qui passe

 

[patricktoulon]

par contre je viens de me rendre compte que vous n'utilisez pas l'argument 3 pour le string
.Max(.IfError(.Match("z", rng, 1), 1)

j'ignorais que par défaut c’était 1 , j'aurais plutôt pensé que c’était 0

 

[mapomme]

j'ignorais que par défaut c’était 1 , j'aurais plutôt pensé que c’était 0

Je ne me suis rendu compte seulement très récemment que la valeur par défaut était 1. Comme quoi, on ne lit jamais assez attentivement l'aide Krosoft. Soit dit en passant, l'aide VBA en ligne (Excel360) est une vraie galère, soit elle tourne sans rien afficher, soit elle affiche un tas de choses inutiles. je vais plus vite par Gogole.

 

[patricktoulon]

Bonjour @mapomme
donc selon toi c'est mieux de le mettre ou pas ?
edit
un peu d'optional ca fait pas de mal
pour l'argument 2

1. omis ou false ="neprend pas les numeriques en compte "
2. true=prend les numerique en compte

Sub test()
MsgBox LastValueOnRange([A:A], False)
End Sub
Function LastValueOnRange(rng As Range, Optional WithNumeric As Boolean = False) 'par vba application
With Application:    LastValueOnRange = .Max(.IfError(.Match("z", rng, 1), 1), IIf(WithNumeric, .IfError(.Match(9 ^ 99, rng, 1), 0), 0)): End With
End Function

 

[mapomme]

Re,
J'ai tendance à mettre tous les paramètres, ne serait-ce que pour que ce soit clair pour tout le monde y.c. les débutants. Évidemment ne pas l'avoir mis ci-dessus est une "exception à la règle", mais aucun membre de cette discussion n'est débutant (il faut bien que je justifie l'irrespect de ma propre règle )

 

[patricktoulon]

re
oui moi aussi je met toujours tout les arguments
je l'ai modifié la prise en compte des numériques est optionnelle
je cherche a ajouter aussi l'option ne pas prendre la valorisation string vide

 

[Dudu2]

Bonjour patricktoulon,

Tu y étais presque en effet...

sous les deux formes vu que perso je préfère evaluate

Pour info:
- 1000 fois LastValueOnRange([A:A]) = 0.10 seconde approx.
- 1000 fois LastValueOnRange2([A:A]) = 0.20 seconde approx.

Moi je dis que quand on peut économiser 1/10.000ème de seconde et ben on s'en prive pas !

 

[patricktoulon]

bonjour @Dudu2
je sais pas perso, j'arrive bien a coder un evaluate à main levée
une idée pour rendre optional le string vide ?
car j'ai plutôt l'impression que le match choppe plutôt la dernière formule
du moins c'est comme çà qu'il faut l'interpréter je crois
je sais que toi c'est ce que tu voulais mais cette option serait un plus

 

[Dudu2]

Je crois qu'il choppe plutôt la chaine (VartType de la cellule = 8) car il ne voit pas les formules à résultat numérique et avec .IfError(.Match("z", Columns(1), -1/0/1), 0) je ne vois pas de solution pour neutraliser la chaine vide.

 

[mapomme]

je ne vois pas de solution pour neutraliser la chaine vide.

Pour le fun, "à la mimine", voir ici (explications dans le fichier - choisir à la fin effacer et non pas supprimer)

 

[patricktoulon]

et oui il y a une couillette dans le potage avec match et chaîne vide
dans le sens ou
MsgBox Application.Match("", [A1:A1000], (-1 ou 0 ou 1))donnera toujours la première occurrence si il y a au moins une occurrence
donc pour le zapper, je pense qu'il faut passer par autre chose que match

 

[Dudu2]

C'est aussi ce que je me suis dit

Pour info j'ai encore modifié le fichier du Post #1 pour quelques ajustements du code des fonctions.
C'est un manie de procéder en incrémental, une vraie galère pour les utilisateurs

 

[patricktoulon]

re j'avais deja tout ca dans mon didacticiel perso
la dernière valeur string non null
MsgBox Evaluate("MAX(ROW(A1:A30)*(A1:A30<>""""))")

et celle ci saute les valeurs 0
MsgBox Evaluate("MAX(ROW(A1:A30)*(A1:A30<>"""")*(A1:A30<>0))")

reste a intégrer le shunte des valeurs numeriques >0