Compter le nombre de cellules dans une colonne filtrée

Version imprimable

13/02/2020, 08h31
révèr

Compter le nombre de cellules dans une colonne filtrée

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 ! :calim2:

Merci et bonne journée !
13/02/2020, 08h49
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 :aie:

Code:

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 :D :mrgreen:

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:

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

Bonjour Patrice,

Voici le code complet modifié et qui fonctionne

Code:

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 :mouarf: )

13/02/2020, 09h45
Philippe Tulliez

1 pièce(s) jointe(s)

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

Pièce jointe 538239
13/02/2020, 10h00
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 !

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:

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.

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

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
Citation:

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

13/02/2020, 10h36
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 :D

Merci à vous deux !

Citation:
Envoyé par 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:

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 ?

13/02/2020, 11h42
Patrice740

Citation:

Envoyé par révèr

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.
13/02/2020, 11h46
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
13/02/2020, 12h07
Patrice740
Citation:

Envoyé par révèr

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:

1 2 For i = 1 To t.ListColumns.Count For i = 1 To t.ListRows.Count
Mais pour chercher, utilises .Find

Citation:
Envoyé par Patrice740
Avec un tableau, pas besoin de chercher DerLgn ou DerCol :
Code:

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:

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 !