XL 2019 VBA Range().SpecialCells(xlCellTypeVisible).Rows

[MilkaQuercy]

Bonjour,

Je rencontre un problème. Je boucle sur les lignes visibles d'un tableau avec la méthode Range.SpecialCells(xlCellTypeVisible).Rows
Tableau sur lequel j'applique des filtres via la méthode Range.AutoFilter.

Dans l'exemple ci-dessous, la variable "Tab" correspond au nom de l'onglet. La variable "TableName" correspond au nom du tableau dans lequel je travail.
SortList() est un tableau Array de type string. Je vous passe l'étape dans laquelle je le remplie. L'opération de filtrage de mon tableau "TableName" fonctionne.

Admettons que mon tableau fait 10 lignes. Si les lignes filtrées visibles sont consécutives, la 3ème & la 4ème. La variable "J" va bien me renvoyer "2" pour 2 lignes visibles. La boucle va bien effectuer 2 tours.
Mon problème est le suivant : si les lignes filtrées visibles ne sont pas consécutives, la 3ème et la 5ème par exemple.
Dans ce cas la variable "J" va renvoyer "1" là où j'aimerais qu'elle me renvoie "2" pour 2 lignes visibles. La boucle va boucler plusieurs fois sur la 3ème ligne et plusieurs fois sur la 5ème. Donc la variable "I" va renvoyer "3" sur plusieurs tour avant de passer à "5" sur plusieurs tour et enfin sortir de la boucle. Alors que je devrais effectuer 2 tours, un pour la ligne 3 puis un pour la ligne 5.
Suivant les cas et essaies que j'ai pu faire, elle boucle plusieurs fois sur la même ligne : 6, 9 ou 12 fois. Multiple de 3...

Est-ce que cette méthode Range.SpecialCells(xlCellTypeVisible).Rows ne fonctionne que sur des lignes visibles successivent?
Est-ce que je fais une erreur, est-ce qu'il me manque un argument?

Je ne peux pas poster mon fichier, si besoin je peux en créer un avec la même fonction, ce serait très rapide.

Dim Ws As WorkSheet
Dim I, J As Integer
Dim Tab, TableName As String

Set Ws = WorkSheets(Tab)

Ws.ListObjects(TableName).Range.AutoFilter Field:=13, Criteria1:=SortList(), Operator:=xlFilterValues

J = Range(TableName).SpecialCells(xlCellTypeVisible).Rows.Count

For Each Line In Range(TableName).SpecialCells(xlCellTypeVisible).Rows
    I = Line.Row
Next Line

 

[patricktoulon]

re
Bonjour
nous avons vu encore récemment ( avec un exemple de @Dudu2) que specialcell ne gérait pas en toute circonstances pas le phenomeme "d'event propagation" il y a des répétitions

For Each Line In Range(TableName).SpecialCells(xlCellTypeVisible).Rows
    I = Line.Row
Next Line

Donc !!!!
a boucler sur les rows visibles (perso) je ne m'en servirais pas
exemple

Sub test()
    TableName = "Tableau1"
    For Each Line In Range(TableName).Rows
        If Line.Hidden = False Then
            I = Line.Row
' ce que tu veux en faire ici           
 'Debug.Print Line.Address(0, 0)' juste pour voir les address de ligne dans la console
        End If
    Next Line
End Sub

 

[Robert]

Bonjour Milka, bonjour le forum,

Le fichier qui va bien serait le bienvenu (comme toi d'ailleurs)... Ton code ne permettant pas de résoudre le problème puisque les variables Ws, Tab et Tablename ne sont pas définies et je n'ai pas envie de recréer ton environnement, c'est à toi de nous le fournir.
Au passage Tab étant un mot clé VBA tu ne pourras pas l'utiliser comme variable...

 

[TooFatBoy]

Bonjour,

Je me demande si tu ne boucles pas sur chaque cellule visible.
Donc si tu as 3 colonnes dans ton tableau, tu vas boucler 3 fois pour chaque ligne visible.

Je ne suis vraiment pas sûr, donc c'est à vérifier...

 

[Efgé]

Bonjour à tous

specialcell ne gérait pas en toute circonstances pas le phenomeme "d'event propagation" il y a des répétitions

Quel problème avez-vous constaté ?
Pour moi

ListObjects("Tableau1").DataBodyRange.SpecialCells(xlCellTypeVisible).Copy

devrait être opérationel.

Cordialement

 

[patricktoulon]

apres
mais là je suppute car encore une fois perso je trouve pas cela utile
analysons cette ligne de code
For Each Line In Range(TableName).SpecialCells(xlCellTypeVisible).Rows
si je devais traduire quasiment mot à mot
pour toutes les lignes des cellules visibles

il est évident que plus ton tableau a de colonnes plus les même lignes vont ressortir dans la boucle

mais là je suppute entendons nous bien

mais le test qui suit tente a confirmer ce que je dis
je vais filtrer mon tableau et je vais déclarer AVEC UN SET!!!! la plage visible
et je vais boucler simplement sur les ligne et écrire l'adresse des lignes dans la console

voyons voir la demo parle d'elle même

voilà tu sais tout

il y a une différence en tre boucler sur les lignes de tout les cells visible
et toute les ligne d'un range non contiguës

 

[job75]

Bonjour MilkaQuercy,

En effet les lignes visibles pouvant être disjointes il ne faut pas utiliser les Rows.

Utilisez les cellules d'une seule colonne :

Sub Filtrer()
Dim Ws As Worksheet
Dim I As Long, J As Long
Dim Tabl As String, TableName As String
Dim SortList()
Dim c As Range

Tabl = "Feuil1"
TableName = "Tableau1"
SortList = Array("a", "b", "c")
Set Ws = Worksheets(Tabl)

Ws.ListObjects(TableName).Range.AutoFilter Field:=13, Criteria1:=SortList(), Operator:=xlFilterValues

J = Range(TableName).Columns(1).SpecialCells(xlCellTypeVisible).Count

For Each c In Range(TableName).Columns(1).SpecialCells(xlCellTypeVisible)
    I = c.Row
    MsgBox "Numéro de ligne : " & I 'pour tester
Next c

MsgBox "Nombre de lignes : " & J 'pour tester

End Sub

A+

 

[patricktoulon]

Bonjour @Efgé
c'est pas la même chose avec copy
avec copy tu copie un range non contigues uni automatiquement en interne
ça n'a rien a voir avec l'event propagation quand on boucle sur les specialcells(xltypevisble).rows

et même à la réflexion, c'est même pas un event propag ,c'est normal tout court si mes supputations sont exactes
exemple j'ai un tableau A1 :B10 je masque les ligne 3 6 et 8 par exemple par un filtre ou autres
je boucle sur les specialcells(xltypevisible).rows
il est normal que dans la boucle la ligne 1 ou 2 ou 4 ou 5 ou 7 ou 9 ou 10 me ressorte 2 fois
A1 et B1 c'est la même ligne elle ressort 2 fois
A2 et B2 c'est la même ligne elle ressort 2 fois
etc..... pour toutes les lignes visibles

 

[MilkaQuercy]

Bonjour à tous,
Merci, ça fait beaucoup de réponse en peu de temps. Je vais tenter de répondre à chacun d'entre vous.

@patricktoulon c'est intéressant. J'ai déjà envisagé de boucler sur le tableau complet et de ne considérer que les lignes non cachées. "If Line.Hidden = False Then" Cependant le tableau fait déjà 2000 lignes il est amené à doubler à therme. J'essaie de trouver les méthodes les plus rapides et efficace. Et boucler sur les lignes visibles me semble être la plus rapide dans ce cas.

@Robert J'étais entrain de reproduire mon cas, sur un fichier vierge, afin de vous le communiquer. Et là le drame. La méthode .count me renvoie le bon nombre de lignes visibles si elles sont consécutives. Si elles ne le sont pas, seulement le premier groupe. (si ligne 3, 4 & 6 visible, me renvoie 2). Cependant la boucle fonctionne bien sur ce fichier test (le code ci-dessous). Je reviens donc sur mon fichier d'origine, et la miracle sans rien modifier, la boucle fonctionne. Je conclue donc que cette méthode n'est pas fiable...

@Marcel32 non je ne boucle pas sur chaque cellule visible mais bien sur chaque ligne. Rien à voir avec le nombre de colonne, car le tableau de mon fichier d'origine a plus de 100 colonnes.

Pour conclure, je pensais avoir trouver une méthode simple pour boucler sur des lignes visibles. Il semble qu'il faille faire compliquer pour y arriver.

Sub Test()
Dim I, J As Integer

J = Range("Tableau").SpecialCells(xlCellTypeVisible).Rows.Count

For Each Line In Range("Tableau").SpecialCells(xlCellTypeVisible).Rows
    I = Line.Row
Next Line

End Sub

 

[patricktoulon]

bonjour @job75
ou un range non contiguë soit par une variable range soit par le range (address des specialcells)

je répète pour notre amis


soit ta methode sur une colonne

For Each c In Range(TableName).Columns(1).SpecialCells(xlCellTypeVisible)

soit par le visible ce qui implique le même nombre de tour de de boucle que de ligne (visible ou pas)

For Each Line In Range(TableName).Rows
        If Line.Hidden = False Then
        '...
        end if 
next

soit par une variable pointant sur les specialcells

Set visiblerang = Range("Tableau1").SpecialCells(xlCellTypeVisible)
For Each ro In visiblerang.Rows
'...
next

soit par le range(address du speciallcell)

Sub test()
     For Each Ro In Range(Range("Tableau1").SpecialCells(xlCellTypeVisible).Address).Rows
        Debug.Print Ro.Address(0, 0)
    Next
End Sub

PS: je précise !! les 4 version ci dessus ont été testées

à @Efgé maintenant
tu devrais tester tes essais avant de les donner
je reprends ton code

et j'ajoute le debug en console

Sub testx()
Dim Rng As Range, Tbl As Variant
Set Rng = ActiveSheet.ListObjects("Tableau1").DataBodyRange.SpecialCells(xlCellTypeVisible)
Tbl = Rng
For i = LBound(Tbl, 1) To UBound(Tbl, 1)
   Debug.Print "ligne " & i
    'Boucle pour faire ce que l'on veux.
Next i
End Sub

resultat en demo
les images parlent plus que des mots

on n'a jamais pu instruire une variable tableau variant directement !!! avec un range non contiguë c'est impossible

je pense avoir tout dit

 

[patricktoulon]

re

@Marcel32 non je ne boucle pas sur chaque cellule visible mais bien sur chaque ligne. Rien à voir avec le nombre de colonne, car le tableau de mon fichier d'origine a plus de 100 colonnes.

ben si justement!!!!!!

on t'a donnée 4 solutions que j'ai résumé prend celle que tu veux et passons a autres chose

 

[MilkaQuercy]

@patricktoulon nous n'avons pas tous le même niveau. Faire preuve de condescendance ne fera avancer personne.

Je suis entrain de tester les 3 premières solutions.
La première fonctionne dans mon fichier test mais pas dans celui d'origine.
La seconde ne fonctionne pas du tout.
Je test actuellement la 3èrme.

 

[TooFatBoy]

@Marcel32 non je ne boucle pas sur chaque cellule visible mais bien sur chaque ligne. Rien à voir avec le nombre de colonne, car le tableau de mon fichier d'origine a plus de 100 colonnes.

OK. Merci pour ton explication.

 

[MilkaQuercy]

Dans le fichier joint, il y 2 onglets avec un tableau chacun :
Onglet nommé "Tableau" avec tableau nommé lui même "Tableau"
Il s'agit d'un onglet et tableau test.
Onglet nommé "Final_Result" avec un tableau nommé lui même "Final_Result".
Il s'agit d'une copie de l'onglet de mon fichier d'origine.

J'ai 2 modules qui ont les mêmes codes.
Le module 1 a 4 macro différentes permettant de boucler sur les lignes visibles du tableau test. 3 fonctionnent.
Le module 2 a les mêmes macros. 3 fonctionnent, presque, on boucle plusieurs fois sur les mêmes lignes.

 

[patricktoulon]

quelle condescendance ???
on t'a donné 4 solution fiables testées sur W10 2016 , W7 2013 , job je crois qu'il a 365
bref si ça ne fonctionne pas chez toi c'est que tu a soit mal exprimé ton besoins soit mal fait ou mal interprété ce que nous avons dis
y a pas besoins d’être une pointure pour faire un copier coller et modifier les noms

pour le fichier destination final nous l'avons pas vu on peut pas deviner ce qui se passe

La première fonctionne dans mon fichier test mais pas dans celui d'origine.
La seconde ne fonctionne pas du tout.

mauvais raisonnement !! ca fonctionne ou pas (mais pas un peu pas du tout )
pas adapté a ce que tu désire vraiment faire peut être
au quel cas ta demande a mal été exprimé
je regarde le fichier