MACRO pour compter lignes renseignées d'une feuille sans tenir compte du filtrage auto

Version imprimable

22/11/2012, 09h40
langeard

MACRO pour compter lignes renseignées d'une feuille sans tenir compte du filtrage auto

Bonjour,

J'ai une feuille excel avec un tableau. Il y a un autofilter sur la première ligne que n'importe quel utilisateur peut avoir modifié.

Je souhaite pouvoir compter le nombre de ligne que mon tableau a réellement quand il n'y a aucun filtrage.

Si le seul moyen et de désactiver temporairement le filtrage, est-il possible de stocker la configuration pour pouvoir la rétablir ensuite (choix de filtrage fait par l'utilisateur).

Je souhaite trouver la solution qui soit la moins lourde possible.

Merci d'avance.
22/11/2012, 15h17
Kimy_Ire

J'ai trouvé ça : http://msdn.microsoft.com/en-us/libr.../ff821564.aspx

Code:

.PivotTables("Tableau croisé dynamique").PivotFields("xxx").ClearAllFilters

Je ne sais pas du tout comment ça fonctionne mais c'est peut-être une base pour travailler ! ^^
22/11/2012, 15h47
Philippe Tulliez

Bonjour,
Je n'avais pas lu dans ta question qu'il s'agissait de Tableau Croisé dynamique.
Pour un filtre effectué sur une table normale
La formule NBVAL(plage) compte le nombre de cellules remplies dans une plage même si cette plage est filtrée.
Donc, si toutes les cellules de la plage A2:A100 de ton tableau contiennent une valeur ou une formule, la formule ci-dessous te renvoie le nombre de lignes remplies.

Code:

=NBVAL($A$2:$A$100)

bonjour,

Après quelques recherches, voici une routine qui répond à mes besoins.

Le principe est simple:
- je teste la présence d'un filtre
- je copie la configuration des filtres (différent si un critère, deux critères, ou plus)
- je désactive les filtres
- je compte les lignes
- je restaure le filtre avec les critères précédemment utilisés

Ce n'est pas optimisé, mais je le poste au cas où ça servirait à quelqu'un d'autre.

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
Sub Count_Worksheet_Lines()
 
'declaration
Dim Fltr As Filter
Dim AutoFilterSettings() As String
Dim NumOfCriteria As Integer
Dim NumOfItem As Integer
Dim MaxCriteria As Integer
Dim CurrentFilter As Integer
Dim Veriftab As Variant
Dim NewCriteria() As String
 
Dim i As Integer, j As Integer
Dim WS As Worksheet
Set WS = ThisWorkbook.Sheets("Sheet1")
 
Dim NumOfLine As Integer
 
'init
MaxCriteria = 0
CurrentFilter = 1
NumOfLine = 0
 
'Check if autofilter is activated and saved settings
If WS.AutoFilterMode = True Then
    'Count item
    NumOfItem = WS.AutoFilter.Filters.Count
    'MsgBox NumOfItem
    ReDim AutoFilterSettings(1 To NumOfItem, 1 To 2)
 
    For Each Fltr In WS.AutoFilter.Filters
        If Fltr.On Then
            NumOfCriteria = Fltr.Count
            If NumOfCriteria > MaxCriteria Then
                MaxCriteria = NumOfCriteria
                ReDim Preserve AutoFilterSettings(1 To NumOfItem, 1 To MaxCriteria + 1)
            End If
            AutoFilterSettings(CurrentFilter, 1) = NumOfCriteria
            'MsgBox NumOfCriteria
            If NumOfCriteria > 2 Then
                For i = 1 To NumOfCriteria
                    AutoFilterSettings(CurrentFilter, i + 1) = Fltr.Criteria1(i)
                Next i
            ElseIf NumOfCriteria = 2 Then
                AutoFilterSettings(CurrentFilter, 2) = Fltr.Criteria1
                AutoFilterSettings(CurrentFilter, 3) = Fltr.Criteria2
            ElseIf NumOfCriteria = 1 Then
                AutoFilterSettings(CurrentFilter, 2) = Fltr.Criteria1
            End If
        Else
            AutoFilterSettings(CurrentFilter, 1) = 0
        End If
    CurrentFilter = CurrentFilter + 1
    Next Fltr
End If
 
'desactivate autofilter
If WS.AutoFilterMode = True Then
    WS.AutoFilterMode = False
End If
'count lines
NumOfLine = WS.Range("F1").End(xlDown).Row
MsgBox NumOfLine
 
'check if Autofiltersetings is empty, if yes exit sub
On Error Resume Next
Veriftab = UBound(AutoFilterSettings)
On Error GoTo 0
 
If IsEmpty(Veriftab) Then Exit Sub
 
'Activate autofilter
WS.Range("A1").AutoFilter
 
'restore previous autofilter
For i = 1 To UBound(AutoFilterSettings, 1)
    If AutoFilterSettings(i, 1) = 1 Then
        WS.Range("A1").AutoFilter Field:=i, Criteria1:=AutoFilterSettings(i, 2)
    ElseIf AutoFilterSettings(i, 1) = 2 Then
        WS.Range("A1").AutoFilter Field:=i, Criteria1:=AutoFilterSettings(i, 2), Operator:=xlOr, Criteria2:=AutoFilterSettings(i, 3)
    ElseIf CDbl(AutoFilterSettings(i, 1)) > 2 Then
        ReDim NewCriteria(AutoFilterSettings(i, 1))
        For j = 1 To AutoFilterSettings(i, 1)
            NewCriteria(j) = AutoFilterSettings(i, j + 1)
        Next j
        WS.Range("A1").AutoFilter Field:=i, Criteria1:=NewCriteria, Operator:=xlFilterValues
    End If
Next i
 
 
End Sub

PS: Je viens de voir les réponses postées, je vais essayer quelque chose avec NBVAL, je ne connaissais pas. Et non je ne parlais pas de TCD.

Merci d'avoir pris la peine de répondre

22/11/2012, 18h06
Philippe Tulliez

Bonjour,
Une autre formule à laquelle je n'ai pas pensé immédiatement et qui répond évidemment exactement à ta demande est la formule LIGNES(plage)
Ainsi si le tableau est compris entre A2 et E16 la formule ci-dessous revoie le nombre de ligne de cette plage.

Code:

=LIGNES($A$2:$E$16)

Si la plage est nommée dbCli

Code:

=LIGNES(dbCli)

Et si c'est une plage transformée en tableau

Code:

=LIGNES(Tableau1)