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.

 

[patricktoulon]

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

 

[job75]

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 :

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]

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]

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

 

[job75]

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 :

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

 

[mapomme]

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]

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 :

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

par :

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]

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

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

par :

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]

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) :

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]

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.

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:

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]

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

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]

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]

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

 

[Dudu2]

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]

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+