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

Dudu2 · 6 Août 2020

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.

mapomme · 8 Août 2020

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

patricktoulon · 8 Août 2020

Bonjour job75
oui solution déjà proposé en plus condensée

job75 · 8 Août 2020

Bonjour le fil,

J'ai supprimé mon message parce qu'en effet la solution avait déjà été donnée.

Voyez le fichier joint, cette solution est sûrement très rapide :

VB:

Function Derlig(colonne As Range)
Dim tablo, i&
Set colonne = colonne.EntireColumn 'sécurité
tablo = colonne.Resize(colonne.Parent.UsedRange.Row + colonne.Parent.UsedRange.Rows.Count) 'matrice, plus rapide, au moins 2 éléments
For i = UBound(tablo) To 1 Step -1
    If Not IsEmpty(tablo(i, 1)) Then Derlig = i: Exit Function
Next
End Function

A+

job75 · 8 Août 2020

Sur le fichier précédent j'ai mesuré les durées du recalcul des formules.

Celle de mapomme en C1 se recalcule en 6,5 ms, la mienne en O1 se recalcule en 5,8 ms.

patricktoulon · 8 Août 2020

Re
@job75 oui on a meme la possibilité de shunter les string =null

job75 · 8 Août 2020

Ah mais j'ai testé en recopiant la plage A2:A15 sur A16:A1400001.

La formule de mapomme se recalcule en 22 ms, la mienne en 48 ms.

C'est bien sûr cette ligne de code qui prend tout le temps :

VB:

tablo = colonne.Resize(colonne.Parent.UsedRange.Row + colonne.Parent.UsedRange.Rows.Count) 'matrice, plus rapide, au moins 2 éléments

mapomme · 8 Août 2020

Bonsoir à tous,

Même pas le courage de tester la solution de @job75 sur ma bécane (31,5° C chez moi, je vais craquer ).
Je pense que la solution de @job75 est la seule pour l'instant qui puisse éliminer la chaine vide. De toute manière, j'ai rarement rencontré des cas où la détermination de la dernière ligne se fait plus d'une fois au cours d'un programme. La durée a donc (selon moi) rarement de l'importance.
On pourrait peut-être déterminer une première limite pour tablo() plus basse (plutôt que celle de UsedRange) mais est-ce bien utile ?

job75 · 8 Août 2020

mapomme à dit:
Je pense que la solution de @job75 est la seule pour l'instant qui puisse éliminer la chaine vide.

Pour éliminer les cellules vides et aussi le texte vide "" il faut remplacer :

VB:

If Not IsEmpty(tablo(i, 1)) Then Derlig = i: Exit Function

par :

VB:

If CStr(tablo(i, 1)) <> "" Then Derlig = i: Exit Function

J'ai mesuré les durées des recalculs uniquement pour voir ce que font les 2 solutions.

job75 · 8 Août 2020

Pour ce qui est de la durée du recalcul j'ai remplacé :

VB:

tablo = colonne.Resize(colonne.Parent.UsedRange.Row + colonne.Parent.UsedRange.Rows.Count)

par :

VB:

tablo = colonne.Resize(140001)

la durée passe de 48 ms à 39 ms, le gain est faible.

C'est donc tout simplement le chargement de la matrice tablo qui prend du temps.

job75 · 8 Août 2020

job75 à dit:
C'est donc tout simplement le chargement de la matrice tablo qui prend du temps.

Alors n'utilisons pas de matrice, voyez ce fichier (2) :

VB:

Function Derlig(colonne As Range)
Dim tablo, i&
Set colonne = colonne.EntireColumn.Cells 'sécurité
With colonne.Parent.UsedRange
    i = .Row + .Rows.Count - 1
End With
For i = i To 1 Step -1
    If Not IsEmpty(colonne(i)) Then Derlig = i: Exit Function
Next
End Function

Avec 140 000 lignes la durée du recalcul de la fonction est maintenant de 19 ms...

Dudu2 · 27 Janvier 2021

Bonjour les Barbaducs,

Je reviens sur ce sujet car utilisant le code de la solution, je me suis rendu compte qu'il y avait un problème.

VB:

NumDerLig = Application.Max(Application.IfError(Application.Match("z", plage.Columns(1)), 0), _
   Application.IfError(Application.Match(9 ^ 99, plage.Columns(1)), 0))

En effet, une valeur de "za" en dernière ligne n'est pas détectée. J'ai donc dû la modifier comme suit:

VB:

NumDerLig = Application.Max(Application.IfError(Application.Match(String(20, "z"), plage.Columns(1), 1), 0), _
   Application.IfError(Application.Match(1.79769313486231 * (10 ^ 308), plage.Columns(1), 1), 0))

J'ai aussi comparé toutes les méthodes que j'ai pu répertorier (voir fichier) exécutées 100 fois sur une colonne de 26 éléments dans un UsedRange de 100.000 lignes.
- Par ColumnDifferences (~ 3.00 sec)
- Par Scan de la colonne (dans le UsedRange) chargée en table mémoire (~2.25 sec)
- Par SpecialCells (~ 2.00 sec)
- Par Match (~0.03 sec)

Donc le Match est pratiquement 100 fois plus rapide (exécuté 1 fois on ne peut même pas détecter sa durée = 0).
La 2ème meilleure méthode est de compter par SpecialCells qui n'a pas la limitation due aux valeurs arbitraires qu'on doit placer dans le Match mais prend quand même plus de 50 fois le temps d'un Match.

patricktoulon · 27 Janvier 2021

re
bonjour @Dudu2
j'ai toujours mis "zzz" et le type (inférieur à) (1)

VB:

Sub test2()
With Application: NumDerLig = .Max(.IfError(.Match("zzz", [A:A], 1), 0), .IfError(.Match(9 ^ 99, [A:A], 1), 0)): End With
MsgBox NumDerLig
End Sub

Dudu2 · 27 Janvier 2021

Bonjour @patricktoulon
Oui Type 1, moi aussi. Oublié et rajouté dans ma correction ci-dessus.
Type 1 est la valeur par défaut de toutes façons.

patricktoulon · 27 Janvier 2021

ben maintenant réessaye "zzz" et 9^99 ca match pour tout

Dudu2 · 27 Janvier 2021

ben maintenant réessaye "zzz" et 9^99 ca match pour tout

Ça marche tant que la dernière ligne ne contient pas "zzza" ou un chiffre > 9 ^ 99 (on peut aller jusqu'à 1299 ^99).
Ok "zzza" c'est plus que rare mais j'ai mis String(20 "z") qui ne fait que repousser la limite.
Par contre pour le chiffre j'ai essayé de mettre le plus grand possible (limites Excel) mais il je n'y arrive pas, les 2 derniers chiffres (58) sont tronqués à la saisie pour arriver à 1.79769313486231 * (10 ^ 308).

Plus grand nombre positif autorisé via formule

1,7976931348623158e+308

job75 · 27 Janvier 2021

Bonsoir Dudu2, le fil,

La formule avec les 2 Match est intéressante justement parce qu'on utilise des constantes "zzz" et 9^99.

Ces constantes sont des limites mais si l'on veut pouvoir les dépasser utiliser une autre méthode, c'est tout.

A+

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

Dudu2

XLDnaute Barbatruc

Pièces jointes

mapomme

patricktoulon

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

Dudu2

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Dudu2

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Dudu2

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Discussions similaires