Fonction pour récupérer le numéro de la n ième ligne visble après un filtre automatique

**LeBo74** · 18/08/2018, 03h54

Bonjour,

Je cherche à créer une fonction pour récupérer le numéro de la n ième ligne visible après l'utilisation d'un filtre sur un tableau structuré.
Je réussi avec cette sub à récupérer l'information que je cherche.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Ligne_tercile()
 
Dim D_Range As Range
Dim ligne As Range
Dim liste_Ligne As New Collection
 
Set D_Range = Range("tableau1[#data]")
 
For Each ligne In D_Range.SpecialCells(xlCellTypeVisible).Rows
liste_Ligne.Add ligne.Row
 
Next
i = 5
Ligne_Tercile_Num = liste_Ligne(i)
Range("B21") = Ligne_Tercile_Num
 
End Sub

Par contre, lorsque je transforme cette sub en fonction, la fonction me retourne la nième ligne du tableau s'en tenir compte du fait quelle soit visible ou non.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Function Ligne_tercile_2(i As Long)
 
Dim D_Range As Range
Dim ligne As Range
Dim liste_Ligne As New Collection
 
Set D_Range = Range("tableau1[#data]")
 
For Each ligne In D_Range.SpecialCells(xlCellTypeVisible).Rows
liste_Ligne.Add ligne.Row
 
Next
 
Ligne_tercile_2 = liste_Ligne(i)
 
End Function

Est-ce normal que la méthode specialcells ne fonctionne pas dans une fonction ou ma synthaxe n'est pas bonne?
Merci de votre aide car je tourne en rond

**boisgontierjacques** · 18/08/2018, 05h39

Bonjour,

C'est ok

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub Essai()
  MsgBox Ligne_tercile_2("Tableau1", 5)
End Sub
 
Function Ligne_tercile_2(NomTableau, i As Long)
 Dim liste_Ligne As New Collection
 Dim ligne As Range
 For Each ligne In Range(NomTableau).SpecialCells(xlCellTypeVisible).Rows
   liste_Ligne.Add ligne.Row
 Next
 Ligne_tercile_2 = liste_Ligne(i)
End Function

Boisgontier

Invité · 18/08/2018, 06h10

Envoyé par boisgontierjacques

Bonjour Jacques,

Si tu appliques ta fonction sur une cellule de l'onglet (ici I3), tu verras que la valeur retournée n'est pas celle fournie en lançant directement Essai depuis l'éditeur VBA.
J'ai constaté cela aussi en essayant de trouver une solution mais je n'y suis pas arrivé.

Pièce jointe 404627

**boisgontierjacques** · 18/08/2018, 07h40

Je ne constate pas le problème

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub Essai()
  [i3] = Ligne_tercile_2("Tableau1", 5)
  [i4] = "version excel:" & Application.Version
End Sub

Boisgontier

Invité · 18/08/2018, 08h22

Envoyé par boisgontierjacques

Est-ce que tu constates la même chose que moi ?

Pièce jointe 404638

**boisgontierjacques** · 18/08/2018, 09h34

Dans le post d'origine, cette fonction est prévue pour être utilisée à partir de VBA.
Curieusement, utilisée dans le tableur, cette fonction renvoie le rang de la ligne visible dans le tableur (???)

Boisgontier

**Pierre Fauconnier** · 23/08/2018, 12h26

Les syntaxes proposées par Ryu sont correctes.

Mais le fait de travailler avec specialcells ne change en rien mes explications. Les syntaxes proposées ne tiennent pas compte du fait que les cellules sont visibles ou pas, et pointeront vers les cellules selon les calculs que j'ai illustré que ces cellules soient visibles ou pas et qu'il y ait des cellules visibles ou pas ne change strictement rien.

**patricktoulon** · 23/08/2018, 13h07

pierre relis mon post

en gros avec areas et cells(x,y) ou cells(position) sur un specialcells(xlvisible) avec en plus une /des colonnes masquées on est chocolat
d'une part par ce que les areas ne seront plus des ligne mais des ranges (xlignes,y colonnes)
et avec plus de 1 colonnes masquée lea areas redeviennent des lignes mais des portions de lignes
ce qui fait que la cells(x,y) de chaque areas ne sera pas la meme qu'une autre areas
autrement dit la cells(x,y) de l'areas(1) ne sera pas forcement sur la meme colonne que cells de l'areas(2)

il me semble d'avoir lu dire quelque chose dans le genre "qu'il ne fallait pas traiter un tableau structuré comme un range" ,c'est la que ca prend tout son sens

**eriiic** · 23/08/2018, 13h26

Bonjour à tous,

c'est pour cela que j'ai dis precedement qui etait preferable de travailler avec l'object listobject et ses listrows

Dans ce cas une suggestion : prévoir une option pour renvoyer au choix la ligne sur feuille ou la ligne de DataBodyRange.
Il ne me semble pas l'avoir vu. C'est une simple soustraction, mais tant qu'à faire...
eric

**Pierre Fauconnier** · 23/08/2018, 13h34

Salut Éric

Envoyé par eriiic

Bonjour à tous,

Dans ce cas une suggestion : prévoir une option pour renvoyer au choix la ligne sur feuille ou la ligne de DataBodyRange.
Il ne me semble pas l'avoir vu. C'est une simple soustraction, mais tant qu'à faire...
eric

Perso, pas compris...

Le databodyrange, du tableau ou d'une colonne, ne tient pas compte des lignes visibles ou masquées (mais je n'ai probablement rien compris de ton intervention...)

**Pierre Fauconnier** · 23/08/2018, 13h31

Envoyé par patricktoulon

[...]d'une part par ce que les areas ne seront plus des ligne mais des ranges (xlignes,y colonnes)[...]

Relis mon billet. Range(X) et Range(X,Y) renvoient une cellule. Offset renvoie une plage de dimensions identiques à la plage d'origine.

Ce que j'ai dit par rapport à la remarque que Ryu, c'est que Range(x), Range(x,y) et Range.Offset(x,y) ne tiennent pas compte des lignes visibles ou masquées. Dès lors, les syntaxes sont parfaitement valides que l'on travaille avec specialcells(xlcelltypevisible) ou pas. Elles ne tiennent simplement pas compte des lignes visibles/masquées. La collection des ListRows ne tient pas non plus compte de cela,et l'objet ListRow.Range non plus.

Cela prouve bien que Microsoft, à tort ou à raison (et à mon avis à raison), n'a pas considéré qu'il était important de travailler, en base de données, en tenant compte des lignes visibles ou pas...

**Pierre Fauconnier** · 21/08/2018, 06h33

Salut.

Tout ça pour ça?

Il est toujours plus rentable de partir d'une base de données saine que de chipoter sur une base polluée

Si je devais faire ton boulot, j'extrairais les données de mon analyse dans un tableau bien propre ainsi que la ligne qui sert de comparaison et je travaillerais sur ce tableau, plutôt que de le laisser pollué par des données d'années non désirées et de chercher des trucs impossibles pour pallier les problèmes inhérents à mes données. Au passage, c'est la base des bases de l'analyse de données, ce que je dis ici

Je rappelle également ici la fonction SOUS.TOTAL qui permet quelques fonctions statistiques de base en ne prenant en compte que les lignes filtrées par le filtre automatique et, selon l'option choisie, les lignes filtrés manuellement.

**RyuAutodidacte** · 21/08/2018, 10h18

Bonjour,

j'ai l'impression q'un tableau croisé dynamique aurait été suffisant pour faire tout cela
peut être juste une macro, en donnant les paramètres voulus (via un procéder que l'on aura déterminé - useform par exemple) pour faire le/les TCD automatiquement voulu(s)
et dans le cas où l'on ne veut pas garder le/les TCD, il est facile de garder seulement les données des TCD en simple valeurs dans un nouveau classeur ou autre

**Pierre Fauconnier** · 21/08/2018, 10h20

Envoyé par RyuAutodidacte

[...]

j'ai l'impression q'un tableau croisé dynamique aurait été suffisant pour faire tout cela[..]

J'y ai pensé aussi. Ca dépend évidemment des analyses qui sont faites, mais le TCD propose déjà tellement de possibilités (en XL2016, en plus...)

**RyuAutodidacte** · 21/08/2018, 12h45

Re,

@LeBo74

Envoyé par LeBo74

Mais, à ce que je constate au niveau des discussions, je reste toujours avec le même problème, la petite fonction que je pensais simple reste introuvable. J'ai essayer les différentes solutions présentées mais j'ai toujours le même fonctionnement : la fonction appelée dans une macro donne le résultat voulu mais appelé à partir d'une feuille, elle ne fonction pas :

Tu n'as pas cherché à comprendre, pour cela il aurait fallu mettre un point d'arrêt au début de la fonction et utiliser la fonction sur ta feuille puis faire un pas à pas (le Debug.Print est conseillé)
Voir le résultat obtenu et comparé avec ce qui est visible sur ton tableau test.
Moi ce que j'ai remarqué c'est que SpecialCells(xlVisible) n'a pas été pris en compte lorsque l'on utilise la fonction sur la feuille.
Pour s'en rendre compte il suffit de prendre le faux résultat et d'ajouter le nombre de ligne masqué se trouvant avant le vrai résultat - si on fait l'addition le compte est bon

Pour l'instant je n'en connais pas la cause …

En reprenant l'exemple de mon post #23 le tableau et le critère (prise en compte de la colonne pour le Crit.)

Nom : Lig_Tab.png
Affichages : 543
Taille : 100,7 Ko

Nom : Lig_Tab.png
Affichages : 543
Taille : 100,7 Ko

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub test()
    MsgBox Lig_Tableau("Table1", "PRODUCTION", 5, 3)
End Sub
 
Function Lig_Tableau(NomTableau$, Crit$, Col&, Ligne&) As Long
'Application.Volatile
With Range(NomTableau)
    For Each Cel In Columns.Item(1).Cells
        If Cel.Offset(, Col - 1) = Crit Then i = i + 1
        If i = Ligne Then Debug.Print Cel.Address: Lig_Tableau = Cel.Row: Exit For
    Next
End With
End Function

Edit : ceci est un code exemple qui marche (que je n'ai pas chipoté), il faudra l'adapter à ta situation, maintenant que tu as le principe (sachant qu'il y a plusieurs possibilités de coder/de procéder … à toi de trouver celle qui te convient)
+remise de la variable "NomTableau" à la place qui lui est dû dans la fonction

**patricktoulon** · 21/08/2018, 13h58

salut ryu
la meme plus universelle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub test()
    MsgBox Lig_Tableau("Table1", "PRODUCTION", 5, 3)
MsgBox Lig_Tableau("Table1", "PRODUCTION", "QUALIF", 3)
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Function Lig_Tableau(NomTableau$, Crit$, Col as variant , Ligne&) As Long
    Dim cel As Range
    With Range("Table1")
        For Each cel In Range("Table1").ListObject.ListColumns(col).Range
            If cel.Value = Crit And cel.EntireRow.Hidden = False Then i = i + 1: If i = Ligne Then Lig_Tableau = cel.Row
        Next
    End With
End Function

**eriiic** · 21/08/2018, 14h22

Bonjour à tous,

en plus de Sous.total() il y a maintenant aussi Agregat() avec de nouvelles possibilités.

J'imagine que tu as ou peux avoir le nom (n° ?) de l'établissement dans une cellule, disons H1.
En filtrant sur les années tu peux avoir la ligne de l'année filtrée la plus basse (si j'ai bien compris il n'y en aura qu'une) avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MIN(SI((Table1[NOM]=$H$1)*(ANNEE(Table1[date])=ANNEE(SOUS.TOTAL(5;Table1[date])));LIGNE(Table1[NOM])))

matricielle à valider avec Shift+Ctrl+Entrée.

Tu peux mettre cette formule dans un nom 'ligRef' et tu l'auras à dispo tout autant pour une formule sur feuille que dans une macro :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part