Microsoft 365 Filtrer dans une variable-tableau : possible ?

[BenHarber]

Bonjour le Forum,
Une petite question de principe (…et je m’en excuse par avance si elle est complètement saugrenue 😌) :

Est-ce qu’il est « VBAment » possible, à partir d’une VARIABLE-TABLEAU (comportant x lignes et y colonnes), de « filtrer » certaines données pour les coller sur une feuille Excel ? (je sais faire la manip à partir d’un tableau Excel mais je me demandais si on pourrait le faire à partir d’une variable-tableau).

Merci d’avance pour vos idées…et votre indulgence !

 

[vgendron]

ajoute cette instruction pour voir
MsgBox "il y a " & .SpecialCells(xlCellTypeVisible).Areas.Count &" plages discontinues dans le résultat filtré"

avec l'enregistreur de macro,
faire un simpe "copier coller" de la plage complète, entraine la copie des cellules visibles==> Excel sait faire
MAIS affecter ca à un tableau vba... VBA ne sait pas faire.. il prend la première area
si tu tiens à passer par un tablo VBA, je pense qu'il faut une boucle sur toutes les lignes du listobject, si visible alors redim preserve tablo....
et coller le tablo final

 

[ChTi160]

Bonjour le fil
Avec un truc du genre :

 Parcourir les zones filtrées pour récupérer les données
On Error Resume Next ' Ignorer l'erreur si aucune cellule filtrée
For Each area In tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Areas
For Each cell In area.Rows

On peut aussi testé s'il y a des lignes visible avant de boucler
Avec un si Databodyrange.height=0 on ne boucle pas ....
Depuis mon téléphone
Je regarde si pas de réponse d'ici que je rentre.
Bonne journée

 

[ChTi160]

Re
Comme promis un exemple tiré d'une de mes procédures! (à adapter)
On trie ,On filtre

Dim Area
Dim R
dim Tab_Temp()
With Range("t_" & YearSelect).ListObject 'Avec le Tableau de la base de Données
         If Not (.DataBodyRange Is Nothing) Or .ListRows.Count = 0 Then  'Si Non Vide       
                  If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData 'On supprime les filtres eventuels
                   With .Sort ‘On va trier le Tableau en fonction des Valeurs des Colonnes « Date » et « Arrivée »du Tableau Structuré
                      With .SortFields
                                .Clear
                                .Add2 Key:=Range("t_" & Year_Select).ListObject.ListColumns("Date").DataBodyRange, _
                          SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
                                .Add2 Key:=Range("t_" & Year_Select).ListObject.ListColumns("Arrivée").DataBodyRange, _
                          SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal                     
                      End With 'SortFields
                               .Header = xlYes ' Le tableau contient des en-têtes
                               .Apply
                   End With 'Sort
'**************************************************************
                   With .Range 'On va Filtrer en fonction de 2 Dates
                           .AutoFilter Field:=Range("t_" & Year_Select).ListObject.ListColumns("Date").Index, _
                                                                            Criteria1:=">=" & DateDebut, _
                                                                                     Operator:=xlAnd, _
                                                                            Criteria2:="<=" & DateFin
                   End With 'Range
'**************************************************************
' Vérifier si des lignes sont retournées par le filtre
         If .DataBodyRange.Height > 0 Then ‘s’il y a au moins une ligne en résultat du Filtre « .Height > 0 »
' Parcourir chaque zone filtrée
 For Each Area In .DataBodyRange.SpecialCells(xlCellTypeVisible).Areas
   For Each R In Area.Rows ‘Parcourir les Lignes de chaque Zone filtrée pour récupérer les données
                Col = Col + 1
      ReDim Preserve Tab_Temp(1 To .ListColumns.Count , 1 To Col) 'On redimensionne le Tableau                      '                          ‘Parcourir les colonnes de "t_xxxx" pour récupérer les valeurs
                          For L = 1 To .ListColumns.Count 'Pour chaque Colonnes
                   Tab_Temp(L , Col)=R.Cells(L) 'pour l'exemple
                                    
                         Next
   Next R
 Next Area

n'hésite pas si besoin
Jean marie

 

[BenHarber]

OK, merci @ChTi160 pour cette solution qui passe par une boucle sur les areas.

Au vu de ton travail -et de la remarque précédente de @vgendron- il semblerait qu'embarquer d'un seul coup les données filtrées dans un tableau VBA ne puisse pas se faire avec une seule ligne de code...😭

 

[job75]

Bonsoir à tous,

Voici une tout autre solution.

Elle n'utilise plus le filtre automatique mais un tri sur une colonne auxiliaire ce qui la rend très rapide :

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim nom$, style$
nom = Sh.ListObjects(1).Name
style = Sh.ListObjects(1).TableStyle
Application.ScreenUpdating = False
With Sheets("Pannes")
    If Sh.Name = .Name Then Exit Sub
    With .ListObjects(1).Range
        .AutoFilter: .AutoFilter 'affiche tout
        Sh.Cells.Delete 'RAZ
        .EntireColumn.Copy Sh.[A1] 'copie tout le tableau
    End With
End With
Sh.ListObjects(1).Name = nom
Sh.ListObjects(1).TableStyle = style
With Sh.ListObjects(1).Range
    .Columns(8).Insert xlToRight 'colonne auxiliaire
    .Cells(2, 8) = "=1/(RC[1]=""" & Replace(Sh.Name, "é", "e") & """)"
    .Columns(8) = .Columns(8).Value 'supprime les formules
    .Sort .Columns(8), xlAscending, Header:=xlYes 'tri pour regrouper et accélérer
    On Error Resume Next 'si aucune SpecialCell
    .Columns(8).SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete 'supprime les lignes des valeurs d'erreur
    .Columns(8).Delete xlToLeft 'supprime la colonne auxiliaire
End With
End Sub

Edit : il n'est pas nécessaire de forcer l'affichage de la ligne d'en-têtes.

A+

 

[job75]

Pour tester j'ai recopié le tableau de la feuille "Pannes" sur 224 000 lignes, l'activation de la feuille "Fabrication" se fait chez moi en :

- 17 secondes sur le fichier Base_Pannes V6

- 2,4 secondes sur le fichier Base_Pannes V7.

 

[TooFatBoy]

Si je comprends bien tu filtres un tableau puis tu copies les lignes visibles dudit tableau, et tu colles ces lignes dans un TS situé dans un autre classeur. J'ai bon ???

Oui c'est bien ça.

Mis du coup tu ne filtres pas une variable tableau comme demandé dans le titre de ta question.

Dommage car j'aurais bien aimé savoir comment réaliser cela proprement et aussi savoir dans quelles proportions ça faisait gagner du temps par rapport au filtrage dans la feuille.

 

[vgendron]

du coup j'en profite pour poster le fichier avec ma solution du post.....#2

 

[job75]

La solution que j'ai donnée au post #50 fonctionne quelles que soient les options du tableau.

Je n'ai plus rien à ajouter sur ce fil.

 

[ChTi160]

Re
Il faut Savoir aussi que les tableaux temporaire(Array()) , sont Limités dans leurs Dimensions Lignes (65536) et Colonnes (65536) lorsque l'on utilise la Méthode "Transpose" à savoir donc combien les Feuilles Cibles vont devoir recevoir de Lignes aux Maximum , avant de faire en sorte que cela fonctionne quelque soit le nombre de lignes a transférer lol.
Jean marie

 

[job75]

Bonjour le forum,

La solution du post #50 dans le cas où la feuille source est dans un autre fichier :

Private Sub Workbook_Open()
Workbook_SheetActivate ActiveSheet 'lance la macro
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim nom$, style$
nom = Sh.ListObjects(1).Name
style = Sh.ListObjects(1).TableStyle
Application.ScreenUpdating = False
On Error Resume Next
With Workbooks.Open(ThisWorkbook.Path & "\Fichier source.xlsx").Sheets(1) 'nom du fichier à adapter
    If Err Then MsgBox "Le fichier 'Fichier source.xlsx' est introuvable !", 4: Exit Sub
    On Error GoTo 0
    With .ListObjects(1).Range
        .AutoFilter: .AutoFilter 'affiche tout
        Sh.Cells.Delete 'RAZ
        .EntireColumn.Copy Sh.[A1] 'copie tout le tableau
    End With
    .Parent.Close False 'ferme le fichier source
End With
Sh.ListObjects(1).Name = nom
Sh.ListObjects(1).TableStyle = style
With Sh.ListObjects(1).Range
    .Columns(8).Insert xlToRight 'colonne auxiliaire
    .Cells(2, 8) = "=1/(RC[1]=""" & Replace(Sh.Name, "é", "e") & """)"
    .Columns(8) = .Columns(8).Value 'supprime les formules
    .Sort .Columns(8), xlAscending, Header:=xlYes 'tri pour regrouper et accélérer
    On Error Resume Next 'si aucune SpecialCell
    .Columns(8).SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete 'supprime les lignes des valeurs d'erreur
    .Columns(8).Delete xlToLeft 'supprime la colonne auxiliaire
End With
End Sub

A+

 

[ChTi160]

Bonjour le Fil
reste plus qu'a répondre à la demande !

Filtrer dans une variable-tableau : possible ?​

Mdr ! Je vais y regarder .
Bonne Journée
Jean marie

 

[BenHarber]

Bonjour à tous,
Ci-joint la façon la plus rapide -et la moins chronophage en terme de lignes de code- que j'ai trouvée pour traiter les données...
...tout en essayant de recoller au sujet initial de ce post ! 😂
Chez moi, le temps de traitement sur 28600 lignes = 1 seconde maxi.
(mais ça m'énerve quand même de faire une manip. -tri- sur le tableau général)

 

[TooFatBoy]

mais ça m'énerve quand même de faire une manip. -tri- sur le tableau général

Qu'est-ce à dire que ceci ??? 🤔

 

[BenHarber]

Bonjour @TooFatBoy ,
Dans l'idéal, j'aurais aimé ne pas du tout "toucher" l'ordre des données du tableau général.