Limites d'Excel

[chanu]

Bonjour à tous,
Je suis en train de réaliser un petit programme avec des filtres sur les colonnes et malheureusement j'ai atteinds le nombre de colonnes maximum. J'ai donc utilisé une autre feuille qui contient le même tableaux mais avec des info différentes sur les colonnes.

Est - il possible de coupler les deux tableaux quand on lance les filtres?

Private Sub btRechercheChantier_Click()

        
    If ListBox1.List(ListBox1.ListIndex) = "Secteur 1" Then
            Workbooks.Open Filename:= _
                "S:\Mon_fichier.xls"
                UserForm1.Hide
            Worksheets(1).Select
            Call Filtre_Vert
    End If
End sub


Private Sub Filtre_Vert()
    Range("E:IV").EntireColumn.Hidden = True
    Range("A:B").EntireColumn.Hidden = True
    Dim Cell As Range
    For i = 0 To ListBox2.ListCount - 1
        If ListBox2.Selected(i) Then
            Dim affiche As Boolean
            affiche = False
            Dim count As Integer
            count = 1
            For Each Cell In Range("E5:IV5")
                If (affiche = True) Then
                    If (count < 6) Then
                        Cell.EntireColumn.Hidden = False
                        count = count + 1
                    End If
                Else
                    If (Cell = CInt(ListBox2.List(i))) Then
                        Cell.EntireColumn.Hidden = False
                        affiche = True
                    End If
                End If
            Next Cell
        End If
    Next i
End Sub

Si quelqu'un a déjà traité ce problème merci de me conseiller.

 

[JNP]

Re : Limites d'Excel

Bonjour Chanu 🙂,
255 colonnes utilisées dans un fichier, toi, au moins, tu plaisantes pas 😛!
Pour les filtres, je ne sais pas, mais ne peux-tu pas utiliser une référence comme en base de donnée, j'imagine que des données sont très répétitives (comme toutes les références au client, adresse, etc. qui se répètent à chaque ligne de commande) ?
Autre solution, basculer sur 2007 qui possède 16384 colonnes, ce qui te laissera de la marge 🙄...
Bon courage 😎

 

[chanu]

Re : Limites d'Excel

Bonjour,
En fait j'utilise le numéro de chantier comme référence par exemple 15110, ce numéro est inscrit en tête de colonne (6 cellules fusionnés dans cette case) et en dessous dans ces 6 colonnes je fais apparaître diverses information.

 

[hoerwind]

Re : Limites d'Excel

Bonsoir,

Sans vouloir faire la publicité d'autres tableurs il est bon de savoir que sous la version 3 de OOo (à télécharger gratuitement sur le Net) chaque feuille comporte 1024 colonnes.

 

[JNP]

Re : Limites d'Excel

Re 🙂,

Bonjour,
En fait j'utilise le numéro de chantier comme référence par exemple 15110, ce numéro est inscrit en tête de colonne (6 cellules fusionnés dans cette case) et en dessous dans ces 6 colonnes je fais apparaître diverses information.

Ne peux-tu pas mettre un petit fichier exemple (sans données confidentielles) ? Je ne situe pas le rapport entre tes 6 colonnes fusionnées, et les 255 colonnes utilisée 😕...
Hoerwind : Alternative intérressante, mais il me semble que ce n'est pas du VBA qui est derrière oOo, d'où reprogrammation necessaire.
Bonne journée 😎

 

[chanu]

Re : Limites d'Excel

Bonjour
Je vous envoie la base de données vide... et le programme que j'ai épuré pour qu'il passe

 

[JNP]

Re : Limites d'Excel

Re 🙂,
Tu l'as peut-être un peu trop épuré, ça beug de partout 😛.
Sacré belle usine à gaz 😉, je comprends mieux (pas encore tout à fait) ton problème...
Je suis pas sûr qu'Excel soit la meilleure solution, j'aurais plutôt essayé de gérer ça en BDD pure (Accès ou SQLServeur par exemple) vu le nombre de données que tu traites. Mais bon, si je comprends bien, au final, tu veux afficher une vue filtrée de tes chantiers, en fonction des chantiers concernés et du type de travaux efectués. De base, tu pourrais entourer tes subs Filtre_Vert et Filtre_Horiz d'une boucle en For Each Feuille dans ton classeur base.xls, du coup, chaque feuille serait filtrée. Mais pour la consultation, ça oblige la personne à afficher plusieurs onglets... Je trouverais plus simple d'utiliser ton Filtre_Vert pour copier chaque chantier qui t'intéresse sur une feuille de consultation, puis de la filtrer en Filtre_Horiz 🙄.
Mais bon, sans modifier grand chose, sur la base de 2 feuilles par secteurs, tu pourrais utiliser

Private Sub btRechercheChantier_Click()
Dim I As Integer, J As Integer
Select Case ListBox1.List(ListBox1.ListIndex)
Case "Bourg"
I = 1
Case "Caluire"
I = 3
Case "Chambéry"
I = 5
Case "Mâcon"
I = 7
Case "Pays de gex"
I = 9
Case "Villefranche"
I = 11
Case Else
Exit Sub
End Select
Workbooks.Open Filename:= _
    "S:\Mon_Fichier.xls"
UserForm1.Hide
Worksheets(I).Select
Call Filtre_Vert
Call Filtre_Horiz
Worksheets(I + 1).Select
Call Filtre_Vert
Call Filtre_Horiz
End Sub

Pour simplifier un peu le code, tu pourrais utiliser un filtre générique

Sub Filtrer(Critère1, Critère2)
    ActiveSheet.Range("$A$6:$B$430").AutoFilter Field:=1, Criteria1:=Critère1
If Critère2 <> "" Then
    ActiveSheet.Range("$A$6:$B$430").AutoFilter Field:=2, Criteria1:=Critère2
End If
End Sub

et à condition de renommer correctement tes CheckBox et OptionButton, ta Filtre_Horiz deviendrait

Private Sub Filtre_Horiz()
Dim MonContrôleOption As Control, MonContrôleCheck As Control
For Each MonContrôleOption In Me.Controls
If TypeName(MonContrôleOption) = "OptionButton" Then
If MonContrôleOption = True Then
Call Filtrer(MonContrôleOption.Name, "")
For Each MonContrôleCheck In Me.Controls
If TypeName(MonContrôleCheck) = "CheckBox" Then
If MonContrôleCheck = True Then
Call Filtrer(MonContrôleOption.Name, MonContrôleCheck.Name)
End If
End If
Next
End If
End If
Next
End Sub

ce qui devrait économiser pas mal de code 😉.
Bon WE 😎

 

[chanu]

Re : Limites d'Excel

Bonjour JNP,
je suis en train de tester tes codes et cela m'a l'air pas mal merci pour tout ce travail.

Edit 1 :
L'idée d'exporter les données sur une feuille de consultation unique serait certainement la meilleure idée. Aurais tu des liens à ce sujet ou des fichiers exemples car je n'ai jamais traité ce type de code.
Merci d'avance

 

[JNP]

Re : Limites d'Excel

Re 🙂,
Si tu cherches copier coller plage sur le forum, tu trouveras certainement ton bonheur. Un fil entre autres : https://www.excel-downloads.com/threads/macro-pour-copier-coller-plusieurs-cellules.113474/
Bon courage 😎

 

[chanu]

Re : Limites d'Excel

J'essaie d'exporter mes données filtrées sur une autre feuille mais ça ne marche pas 🙁

Private Sub Filtre_Vert()
    Range("E:IV").EntireColumn.Hidden = True
    Range("A:B").EntireColumn.Hidden = True
    Dim Cell As Range
    For I = 0 To ListBox2.ListCount - 1
        If ListBox2.Selected(I) Then
            Dim affiche As Boolean
            affiche = False
            Dim count As Integer
            count = 1
            For Each Cell In Range("E5:IV5")
                If (affiche = True) Then
                    If (count < 6) Then
                        Cell.EntireColumn.Hidden = False
                        count = count + 1
                    End If
                Else
                    If (Cell = CInt(ListBox2.List(I))) Then
                        Cell.EntireColumn.Hidden = False
                        affiche = True
                    End If
                End If
                
                With Range("E:IV")
                    .Copy
                    Sheets("Consultation").Range("E5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                End With
            Next Cell
        End If
    Next I
End Sub

J'aimerais en effet que mes colonnes qui ont été filtrées préalablement soient exporter sur la feuille Consultation

 

[JNP]

Re : Limites d'Excel

Re 🙂,
A mon avis, tu fais une erreur de raisonnement, tu n'as plus besoin de masquer tes colonnes. Il te suffit de chercher la première cellule de ton chantier, et si elle fait partie de la liste, copier le chantier complet sur ta feuille de destination. Puis, quand tu as fini de copier tous les chantiers qui t'intéressent, tu filtres verticalement.
Bon courage 😎

 

[chanu]

Re : Limites d'Excel

Re
J'ai testé ce code,

Private Sub Filtre_Vert()
    Dim Cell As Range
    For I = 0 To ListBox2.ListCount - 1
        If ListBox2.Selected(I) Then
            Dim affiche As Boolean
            affiche = False
            Dim count As Integer
            count = 1
            For Each Cell In Range("E5:IV5")
                If (affiche = True) Then
                    If (count < 6) Then
                        Cell.EntireColumn.Hidden = False
                        count = count + 1
                    End If
                Else
                    If (Cell = CInt(ListBox2.List(I))) Then
                        Cell.EntireColumn.Hidden = False
                        affiche = True
                    Cell.EntireColumn.Select
                    Selection.Copy
                    Sheets("Consultation").Select
                    Range("E1").Select
                    ActiveSheet.Paste
                    Call Filtre_Horiz
                    End If
                End If
            Next Cell
        End If
    Next I
End Sub

et le soucis c'est que ça me prend en compte que le premier chantier sélectionné, il y a pourtant un compteur au dessus😕

 

[chanu]

Re : Limites d'Excel

Re je vous remets un fichier plus simplifié
Ce lien n'existe plus
Je n'arrive pas à exporter mes colonnes sur la feuilles consultation, je sais je suis désolé je suis un galèrien😕

 

[JNP]

Re : Limites d'Excel

Re 🙂,
Effectivement, le message a du mal à passer 😀.
Ce que je préconisais, c'était plutôt ça

Dim J As Integer, K As Integer
K = 5
For J = 5 To 255 Step 6
If Cells(5, J) = CInt(ListBox2.List(I)) Then
Range(Cells(5, J), Cells(86, J + 5)).Copy Sheets("Consultation").Cells(K + 5)
K = K + 5
End If
Next I

à adapter un peu éventuellement, et en laissant tomber tes Hidden...
Bon courage 😎
PS : j'ai pas vraiment vu les améliorations proposées mises en place 😱

 

[chanu]

Re : Limites d'Excel

Re et Merci JNP ton aide m'est précieuse cependant deux problèmes persistent, le premier vient du fait que je n'arrive pas à supprimer les colonnes qui ne sont pas sélectionnées (j'ai essayé avec Dercol sans succès). Et le second arrive avec mon filtre vertical, quand je lance la recherche, les colonnes sélectionnées de ma première feuille se collent sur ma feuille "consultation", jusque là tout ce passe bien; et ensuite les colonnes sélectionées de ma seconde feuille se collent sur celles précédemment sélectioné. Et - il possible de de coller les col de la "feuil1" en premier et celles de la "feuil2" à la suite de celles de la "feuil1"?

Private Sub btRechercheChantier_Click()
    RAZ
    RAZ2
    RAZ3
    Dim I As Integer, J As Integer
    Select Case ListBox1.List(ListBox1.ListIndex)
    Case "Secteur A"
    I = 2
    Call Filtre_Vert
    Call Filtre_Horiz
    Case Else
    
    Exit Sub
    End Select
            
                UserForm1.Hide
                Worksheets(I).Select
                Sheets(1).Visible = True
                Call Filtre_Vert
                Call Filtre_Horiz
                
    If I = 2 = True Then
    Worksheets(I + 1).Select
    Call Filtre_Vert
    Call Filtre_Horiz
    End If

    Dim Dercol As Long
    Worksheets(1).Select
    Dercol = ActiveSheet.UsedRange.Columns.count
    Range(Columns(Dercol + 1), Columns("IV")).Delete
    
    'Application.ScreenUpdating = False
    'For I = 2 To ActiveWorkbook.Sheets.count
        'Sheets(I).Visible = xlVeryHidden
    'Next
                        
 End Sub

    '======================================= FILTRES VERT ================================
Private Sub Filtre_Vert()
    Dim Cell As Range
    For I = 0 To ListBox2.ListCount - 1
        If ListBox2.Selected(I) Then
            Dim affiche As Boolean
            affiche = False
            Dim count As Integer
            count = 1
            For Each Cell In Range("E5:IV5")
                If (affiche = True) Then
                    If (count < 6) Then
                        Cell.EntireColumn.Hidden = False
                        count = count + 1
                        Dim J As Integer, K As Integer
                            K = 5
                            For J = 5 To 255 Step 6
                                If Cells(5, J) = CInt(ListBox2.List(I)) Then
                                    Range(Cells(5, J), Cells(86, J + 5)).Copy Sheets("Consultation").Cells(K, J)
                                    K = K + 5
                                End If
                            Next J
                    End If
                Else
                    If (Cell = CInt(ListBox2.List(I))) Then
                        Cell.EntireColumn.Hidden = False
                        affiche = True
                    End If
                End If
            Next Cell
        End If
    
    Next I
    
    Range("C2").Select
    ActiveCell.FormulaR1C1 = ListBox1.List(ListBox1.ListIndex)

End Sub

EDIT : Pour les colonnes vides je viens de trouver ce code mais il ralentit ma recherche

    Dim c
    For c = 256 To 1 Step -1
    Worksheets(1).Select
    If Cells(65536, c).End(xlUp).Row = 1 Then Cells(1, c).EntireColumn.Delete
    Next c

EDIT 2 : J'ai créé un deuxième Filtre_Vert en utilisant

Range(Cells(5, J), Cells(86, J + 5)).Copy Sheets("Consultation").Cells(K, J + 100)

Comme ça j'ai utilisé derrière

If Cells(65536, c).End(xlUp).Row = 1 Then Cells(1, c).EntireColumn.Delete

Et pour montrer à l'utilisateur que le programme tourne pendant son attente, j'ai utilisé une progressbar