Discussion :

[révèr]

Bonjour le forum,

Cette fois-ci, j'ai beau essayer de plein de façons différentes je n'arrive pas au résultat escompté...

Une fois mon filtre réalisé sur mon tableau, j'aimerai compter le nombre de cellules qui composent une colonne en particulier.

Je suis passé par un Application.Subtotal, par un .SpecialCells(xlCellTypeVisible).Count (et Rows.Count)... en définissant ma plage filtrée OU non filtrée. (i.e. : un comptage sur la plage filtrée // comptage sur la plage non-filtrée (en partant sur le xlCellTypeVisible)...et rien n'y fait. Lorsque j'ai plus de 1 ligne dans mon résultat filtré, ma MsgBox m'affiche toujours 1...

Je sollicite donc votre aide dans mon périple !

Merci et bonne journée !

[révèr]

Décidément c'est une manie ! Au bout de 1 heure de recherche intensive sur internet je n'avais rien trouvé et m'étais décidé de poster sur le forum pour avoir de l'aide... pour au final trouver la solution juste après. Désolé de ne poster présentement que des monologues

Code :

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

cc = Application.Subtotal(103, WbO.Columns(9)) - 1

Voici la solution trouvée. J'ai changé le 3 en 103 puisqu'il semblerait que ce soit considéré comme un filtre effectué manuellement (et le -1 pour retirer l'en-tête).

Bonne journée à vous tous

[Patrice740]

Bonjour,

Ton code compte le nombre de cellules filtrées qui contiennent une valeur, pas le nombre de cellules visibles de la colonne !

Essaies plutôt :

Code VBA :

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

1
2
3
4
5
6
7
Option Explicit
Sub Test()
Dim t As ListObject
Dim n As Long
  Set t = Worksheets("Feuil1").ListObjects("MonTableau")
  n = t.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Count
End Sub

[révèr]

Bonjour Patrice,

Voici le code complet modifié et qui fonctionne

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
18
19
20
21
22
23
24
25
26
27
28
Private Sub T_Cons_CB_LF()
    Dim derLig As Integer
    Dim t As ListObject
    Dim n As Long
 
    Set Wb = Workbooks(OB4.Caption & Frame1.Caption)
    Set WbO = Wb.Sheets(T_Cons_TBAC.Text)
    Set t = WbO.ListObjects("MyTable")
 
    derLig = WbO.Cells(Rows.Count, 1).End(xlUp).Row
 
    If IsEmpty(WbO.Range(Cells(4, 6), Cells(derLig, 6)).SpecialCells(xlCellTypeVisible)) Then
        T_Cons_CBCR.Value = WbO.Range(Cells(4, 5), Cells(derLig, 5)).SpecialCells(xlCellTypeVisible)(1).Value
    Else
        T_Cons_CBPO.Value = WbO.Range(Cells(4, 6), Cells(derLig, 6)).SpecialCells(xlCellTypeVisible)(1).Value
    End If
 
    T_Cons_TBSup.Text = WbO.Range(Cells(4, 9), Cells(derLig, 9)).SpecialCells(xlCellTypeVisible)(1).Value
 
    n = t.ListColumns(2).DataBodyRange.SpecialCells(xlCellTypeVisible).Count
 
    If n = 1 Then
        T_Cons_TBVA.Visible = True
        T_Cons_LBVA.Visible = True
        T_Cons_TBVA.Enabled = False
        T_Cons_TBVA.Text = WbO.Range(Cells(4, 26), Cells(derLig, 26)).SpecialCells(xlCellTypeVisible)(1).Value
    End If
End Sub

Je viens de le tester avec la colonne 2 qui est vide dans mon test, et cela fonctionne parfaitement ! Merci beaucoup à toi !

Je me doute que la globalité de mon code peut être revu. Mais je n'ai que peu de temps. Je vais dans un premier temps m'attarder sur le fonctionnement global du programme avant de me lancer dans l'optimisation. J'ai cru voir un petit adage qui disait :

Un code ça... fonctionne, s'optimise (et oui j'ai oublié un mot )

[Philippe Tulliez]

Bonjour,
En ajoutant la ligne des totaux du tableau structuré et en choisissant comme fonction de synthèse Nombre, cela t'aurait permis d'avoir la réponse à ta question

[révèr]

Bonjour Philippe,

En effet j'aurai pu passer par Excel, mais le fait est que le besoin de mon supérieur est que le classeur soit entièrement automatisé. L'utilisateur n'aura aucun droit de modification sur le classeur en tant que tel. A moins que cette ligne de total se mette automatiquement à jour lors d'un filtre réalisé par une saisie utilisateur sur le Userform ?

Si tel est le cas, cette-dite ligne de total porte-t-elle une dénomination particulière en VBA pour en récupérer son contenu ? Ou bien devrais-je lui demander, dans ton exemple, d'aller récupérer la dernière+1 ligne de la colonne M du tableau filtré, (ou dernière si les lignes de totaux sont inscrites dans le tableau structuré).

Dans tous les cas, Merci pour ton exemple. Bien que j'aie toujours du mal avec les TS et que je ne les utilise pas actuellement. J'ai pris l'habitude, qui peut être mauvaise, de continuer à coder de la manière dont ça fonctionne. Je sais que c'est loin d'être optimal, mais au vu des délais qui me sont imposés, cette notion d'optimisation est malheureusement reléguée en second plan. Je dois présenter d'ici 1 petit mois le classeur complètement automatisé... et j'en suis encore loin.

Je dois avouer que je suis perturbé, parce que j'ai peur de m'égarer... de plus, j'ai eu récemment de nouvelles directives comme incorporer un nouveau système de "Codes BG", qui n'existait pas en Année Comptable 2019... ce qui fait que les données pré-2019 sont inexploitables puisque ces codes sont tellement précis qu'il m'est impossible de retrouver le code exact avec les anciennes données.

Désolé si mon message est une métaphore du mur des lamentations, mais...je ne sais plus par quel bout prendre ce projet.

De nouveau, je te remercie pour ton aide et te souhaite une agréable journée !

[Patrice740]

Re,

Faire référence aux ranges (WbO.Range(Cells...) plutôt qu'au tableau est une source d'erreur.

Il faudrait écrire quelque chose comme (non testé) :

Code VBA :

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
18
19
20
21
22
23
24
25
26
27
Private Sub T_Cons_CB_LF()
    Dim Wb As Workbook
    Dim WbO As Worksheet
    Dim t As ListObject
    Dim n As Long
 
    Set Wb = Workbooks(OB4.Caption & Frame1.Caption)
    Set WbO = Wb.Sheets(T_Cons_TBAC.Text)
    Set t = WbO.ListObjects("MyTable")
 
    If IsEmpty(t.ListColumns(6).DataBodyRange.SpecialCells(xlCellTypeVisible)) Then
        T_Cons_CBCR.Value = t.ListColumns(5).DataBodyRange.SpecialCells(xlCellTypeVisible)(1).Value
    Else
        T_Cons_CBPO.Value = t.ListColumns(6).DataBodyRange.SpecialCells(xlCellTypeVisible)(1).Value
    End If
 
    T_Cons_TBSup.Text = t.ListColumns(9).DataBodyRange.SpecialCells(xlCellTypeVisible)(1).Value
 
    n = t.ListColumns(2).DataBodyRange.SpecialCells(xlCellTypeVisible).Count
 
    If n = 1 Then
        T_Cons_TBVA.Visible = True
        T_Cons_LBVA.Visible = True
        T_Cons_TBVA.Enabled = False
        T_Cons_TBVA.Text = t.ListColumns(26).DataBodyRange.SpecialCells(xlCellTypeVisible)(1).Value
    End If
End Sub

@Philippe : « j'aimerai compter le nombre de cellules... » [visibles], pas uniquement celles qui contiennent une valeur.

[Philippe Tulliez]

Bonjour,
Pour obtenir un élément de la ligne des totaux d'un tableau structuré, celle-ci doit être affichée

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub t()
  Dim oList As ListObject
  Set oList = shtDb.ListObjects(1)
  With oList
  .ShowTotals = True   ' Affiche la ligne des totaux
    With .ListColumns("Voiture").Total
     Debug.Print .Formula  ' Affiche la formule
     Debug.Print .Value      ' Affiche le résultat renvoyé par la formule
    End With
  End With
 Set oList = Nothing
End Sub

Bien que j'aie toujours du mal avec les TS et que je ne les utilise pas actuellement. J'ai pris l'habitude, qui peut être mauvaise, de continuer à coder de la manière dont ça fonctionne

Cela vaudrait la peine de "perdre" un peu de temps pour bien comprendre les tableaux structurés car cela te faciliterait la vie et te permettrait d'avancer plus vite ensuite dans la programmation et surtout dans la maintenance de ton projet

[révèr]

Patrice, ton code fonctionne parfaitement ! Je vais tenter de l'adapter dans toutes les autres procédures.

Philippe, je vais suivre tes conseils et m'atteler à la compréhension des Tableaux Structurés. J'ai vu qu'il y avait des tutoriels les concernant. Je vais m'enquérir de ces connaissances

Merci à vous deux !

[révèr]

Re,

Faire référence aux ranges (WbO.Range(Cells...) plutôt qu'au tableau est une source d'erreur.

Il faudrait écrire quelque chose comme (non testé) :

Code VBA :

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
18
19
20
21
22
23
24
25
26
27
Private Sub T_Cons_CB_LF()
    Dim Wb As Workbook
    Dim WbO As Worksheet
    Dim t As ListObject
    Dim n As Long
 
    Set Wb = Workbooks(OB4.Caption & Frame1.Caption)
    Set WbO = Wb.Sheets(T_Cons_TBAC.Text)
    Set t = WbO.ListObjects("MyTable")
 
    If IsEmpty(t.ListColumns(6).DataBodyRange.SpecialCells(xlCellTypeVisible)) Then
        T_Cons_CBCR.Value = t.ListColumns(5).DataBodyRange.SpecialCells(xlCellTypeVisible)(1).Value
    Else
        T_Cons_CBPO.Value = t.ListColumns(6).DataBodyRange.SpecialCells(xlCellTypeVisible)(1).Value
    End If
 
    T_Cons_TBSup.Text = t.ListColumns(9).DataBodyRange.SpecialCells(xlCellTypeVisible)(1).Value
 
    n = t.ListColumns(2).DataBodyRange.SpecialCells(xlCellTypeVisible).Count
 
    If n = 1 Then
        T_Cons_TBVA.Visible = True
        T_Cons_LBVA.Visible = True
        T_Cons_TBVA.Enabled = False
        T_Cons_TBVA.Text = t.ListColumns(26).DataBodyRange.SpecialCells(xlCellTypeVisible)(1).Value
    End If
End Sub

Suite à ton code, j'ai essayé de l'adapter à mes autres procédures et justement... Je rencontre de nouveau un problème.
Il se trouve que pour l'initialisation de mes ComboBox de sites, je n'ai pas le même nombre de sites pour chaque région. Ainsi, je me retrouve avec des cellules vides dans ma liste déroulante. Y a-t-il moyen d'utiliser le .SpecialCells(xlCellTypeBlanks) en " NOT " ? Ou alors dois-je m'y prendre autrement ?

[Patrice740]

Y a-t-il moyen d'utiliser le .SpecialCells(xlCellTypeBlanks) en " NOT " ? Ou alors dois-je m'y prendre autrement ?

Si ce sont des valeurs qu'il y a dans ton tableau :
.SpecialCells(xlCellTypeConstants)
si ce sont des formules, il faut passer par un tableau VBA.

[révèr]

Merci Patrice !

Le xlCellTypeConstants a complètement réglé le problème. Merci énormément ! Désormais je serai déjà beaucoup plus flexible sur ce point

Y aurait-il une alternative pour parcourir les lignes sans avoir à recourir à une variable "derCol" ?

Actuellement, l'utilisateur se voit attribuer une région. L'algorithme va par la suite parcourir la ligne des régions à la recherche de la colonne où appartient la région en question. Il utilisera par la suite cette dernière colonne comme paramètre.

Est-il donc possible, par une gymnastique similaire à celle que tu m'as proposée pour les colonnes, de faire de même pour les lignes ?

J'ai essayé en tapant .ListRows mais les méthodes qui y sont associées sont radicalement différentes de celles pour .ListColumns

[Patrice740]

Y aurait-il une alternative pour parcourir les lignes sans avoir à recourir à une variable "derCol" ?

Avec un tableau, pas besoin de chercher DerLgn ou DerCol :

Code :

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

1
2
  For i = 1 To t.ListColumns.Count
  For i = 1 To t.ListRows.Count

Mais pour chercher, utilises .Find

[révèr]

Avec un tableau, pas besoin de chercher DerLgn ou DerCol :

Code :

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

1
2
  For i = 1 To t.ListColumns.Count
  For i = 1 To t.ListRows.Count

Mais pour chercher, utilises .Find

De retour de ma pause repas, plus requinqué que jamais ! Ravi d'avoir pu avoir ta réponse pendant ce temps, j'ai pu reprendre directement mon clavier et mes délicieuses lignes de code ! Voici à quoi j'en suis arrivé. Oui, tu remarqueras qu'il y a "WbO" et "Wb" dans toutes les procédures. Je les déclarerai en Option Explicit ultérieurement, quand j'aurai fait fonctionner les procédures comme je le souhaite.

J'ai réussi à utiliser la méthode .Find après de multiples échecs. J'étais parti sur un .Rows(1)...avant de réaliser que les valeurs cherchées faisaient partie des en-têtes, et que ces en-têtes sont à part des données du TS. Du coup direction .HeaderRowRange !

La suite avec le xlCellTypeConstants dont tu m'as parlé avant ma pause repas !

Et cela fonctionne très bien !!! Sûrement l'une des procédures les plus optimisées parmi toutes celles que j'ai écrites

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
Private Sub T_Cons_CBSite_Init()
    Dim t As ListObject
    Dim CBS As Range, CellCBS As Range, LF As Range
    Dim WbO As Worksheet
 
    Set Wb = ThisWorkbook
    Set WbO = Wb.Sheets(2)
    Set t = WbO.ListObjects("t_site")
 
    Set LF = t.HeaderRowRange.Find(Frame1.Caption, LookIn:=xlValues, lookat:=xlWhole)
 
    Set CBS = t.ListColumns(LF.Column).DataBodyRange.SpecialCells(xlCellTypeConstants)
 
    For Each CellCBS In CBS
        T_Cons_CBSite.AddItem (CellCBS.Value)
    Next
End Sub

Merci beaucoup à toi !