XL 2013 Variable tableau ''DataRange'' sur lignes non-masquées ?

[BenHarber]

Bonjour le Forum,

Depuis quelques temps, j’ai pris l’habitude de travailler avec des variables tableaux pour accélérer la réalisation de mes procédures.

J’ai notamment adopté cette commande suivante en début de sub :

Dim DataRange As Variant
DataRange =sheets(1).Range("A2:Z1000”).Value ‘Par exemple
(Cette commande me permet ensuite de passer en revue toutes les lignes et colonnes de ma feuille pour leur appliquer un traitement, QUE CES LIGNES OU COLONNES SOIENT, dans ma feuille d’origine, MASQUEES OU NON).

Je souhaiterais maintenant savoir, dans le cadre de données filtrées, s’il existe une commande qui importerait dans le DataRange UNIQUEMENT LES LIGNES NON MASQUÉES de ma feuille ?
J'ai bien essayé : DataRange =sheets(1).Range("A2:Z1000”).SpecialCells(xlCellTypeVisible).Value mais DataRange ne me renvoie qu'une ligne.

Merci d’avance pour vos idées et suggestions.

BH

 

[sylvanu]

Bonjour BenHarber,
Peut être une solution détournée en testant si la ligne est masquée ou non :

Sub Essai()
Dim Datarange(20), n As Integer, i As Integer
n = 1   ' init index de stockage dans array
For i = 1 To 20
    If Sheets("Feuil1").Rows(i).Hidden = 0 Then
        Datarange(n) = Sheets("Feuil1").Range("A" & i)  ' stocke si ligne non masquée
    n = n + 1
    End If
Next
End Sub

Mais peut être qu'une solution "une formule" existe ...

 

[BenHarber]

Bonjour Sylvanu,
Merci pour ton intérêt et ta proposition. ☺
Effectivement, j'ai pensé à faire comme tu l'indiques, mais l'ennui, en passant par une boucle For/Next, c'est que ça ralentit pas mal l'exécution de ma macro.
J'avoue que j'ai un fichier qui va jusqu'à 900 000 lignes...

 

[sylvanu]

Je m'en doutais un peu, mais n'ai rien trouvé pour faire un transfert automatique matrice filtrée/array.
N'est il pas possible de faire un transfert global puis faire un filtre sur l'array avec les règles de filtrage appliquées à la feuille ?
Ou attendre le bon samaritain, si tant est qu'une telle formule existe.

 

[BenHarber]

...heu...un filtre sur l'array ? Jamais fait ☹
Faut dire que je me débats déjà pas mal avec un tableau simple....
Wait and see....

 

[sylvanu]

Je pensais simplement ....
Le travail sur Array est rapide, donc je transfert tout dans tablo1 puis je filtre les valeurs de tablo1 avec la même règle de filtrage que sur la feuille et je range dans tablo2.
Mais sans connaitre la structure de votre fichier, ça ne marche que si les règles de filtrage sont connues d'avance.

 

[sylvanu]

Alors très vicieux mais ça marche. A voir pour le temps d'exécution :
Cette formule copie bien la zone filtrée dans une autre feuille.

Sheets("Feuil1").Range("A1:A20").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Feuil2").Range("A1")

Alors en trichant :
1- on copie la zone filtrée dans une autre feuille, la zone collée est filtrée mais sans ligne masquée.
2- on calcule la taille de la zone filtrée
3- on transfert cette table en array. Ca marche puisque cette seconde zone est non filtrée :

Sub Essai2()
Sheets("Feuil1").Range("A1:A20").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Feuil2").Range("A1")
DerLig = Sheets("Feuil2").Cells(Rows.Count, 1).End(xlUp).Row
Datarange = Sheets("Feuil2").Range("A1:A" & DerLig)
End Sub

Je vous le concède, c'est tiré par les cheveux.
Mais sur mon vieux PC, sur 900000 lignes avec que 16 lignes non masquées, ça prends 0.117s.
Mais le temps dépend du nombre de lignes non masquées.

 

[BenHarber]

OK Sylvanu : pourquoi pas ?
"Faute de grives, on mange des merles"....
J'essaierai cette proposition et vous tiendrai au courant...Merci !

 

[sylvanu]

J'ai updaté mon post.

 

[eriiic]

Bonjour à tous,

à force de tourner en rond j'ai trouvé une autre possibilité.
Le .Value ne retourne qu'une ligne mais le range est correct.
On peut donc récupérer toute la table DataBodyRange et boucler que sur les lignes visibles :

    Dim datas, plV As Range, c As Range
    datas = ActiveSheet.ListObjects("Tableau1").DataBodyRange.Value
    Set plV = ActiveSheet.ListObjects("Tableau1").DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)
    For Each c In plV
        Debug.Print datas(c.Row - plV.Row + 1, 1) ' lignes visibles de DataBodyRange (champ 1)
    Next c

eric

 

[eriiic]

Un autre temps, j'aurais peut-être eu un merci pour le temps passé.
Mais de rien BenHarber, ce fut avec plaisir !

 

[BenHarber]

Bonjour @eriiiic ,
Je suis désolé je n'ai pas eu encore le temps d'essayer la solution, on m'a dépêché sur un dossier plus urgent
Merci de ta proposition qui semble convenir parfaitement.
Je suis conscient du temps que tu as passé à rédiger une proposition et ne comptais pas te laisser en reste.
Je ferai un retour dès que j'aurai testé ton code, même si ce n'est pas dans l'immédiat.
Encore merci.