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

  • Initiateur de la discussion Initiateur de la discussion Dudu2
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

Dudu2

XLDnaute Barbatruc
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.
 

Pièces jointes

Dernière édition:
Solution
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
Le fichier mis à jour du Post #1 contient toutes les options et codes de ce fil avec notation.

2020-08-08_113513.jpg
 
Dernière édition:
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
VB:
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
 
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.
 
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
VB:
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
 
Dernière édition:
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 😡)
 
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
 
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.
 
Dernière édition:
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
 
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
 
Dernière édition:
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

Réponses
5
Affichages
563
Réponses
9
Affichages
1 K
Retour