Discussion :

[informer]

Bonjour,

Je cherche à optimiser l'utilisation de l'autofilter. J'ai le code suivant qui réduit la plage au nombre de lignes dont la dernière cellule de la colonne

NomColRef

est non vide. Les valeurs sont continues dans cette colonne!

Code :

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

1
2
3
4
5
6
7
8
9
 
lXlsRowNumber = .Cells(.Rows.Count, .Range("NomColRef").Column).End(xlUp).Row
Set oXlsRangeCurrentRegion = .Range("A1").CurrentRegion.Resize(RowSize:=lXlsRowNumber)
 With oXlsRangeCurrentRegion
                .AutoFilterMode = True
                lIdxCol = .Range("NomColCrit").Column 
                .AutoFilter Field:=lIdxCol, Criteria1:=IIf(Nz(oRecSet![Value], "") = "", "=", oRecSet![Value]) 
               Set xlsRangeAutoFilter = .SpecialCells(xlCellTypeVisible)
End With

J'espérais que

.AutoFilter Field:=lIdxCol, Criteria1:=IIf(Nz(oRecSet![Value], "") = "", "=", oRecSet![Value])

Ne se ferait que sur

oXlsRangeCurrentRegion

Mais quand le critère est Blank

Criteria1:="="

alors ça filtre sur la totalité des lignes de la feuille soit 1047585 lignes


Merci par avance pour toute aide

[Theze]

Bonjour,

Peut être en définissant la plage sur toute la zone qui contient des valeurs à partir de A1 (je n'ai fait aucun test !) :

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
 
Sub Test()
 
    Dim Plage As Range
 
    With ActiveSheet
 
        Set Plage = .Range(.Cells(1, 1), _
                    .Cells(.Cells.Find("*", .[A1], -4123, , _
                    1, 2).Row, .Cells.Find("*", .[A1], -4123, , _
                    2, 2).Column))
 
    End With
 
     With Plage
 
        .AutoFilterMode = True
        lIdxCol = .Range("NomColCrit").Column
        .AutoFilter Field:=lIdxCol, Criteria1:=IIf(Nz(oRecSet![Value], "") = "", "=", oRecSet![Value])
        Set xlsRangeAutoFilter = .SpecialCells(xlCellTypeVisible)
 
    End With
 
End Sub

[informer]

Bonjour theze,

Merci pour ton aide

Je précise que debug.print lXlsRowNumber affiche bien le nombre de lignes <> vide sachant que les données sur la colonne nomColRef sont continues de lignes en lignes, jamais de cellules vides.

Par contre nomColCrit peut être vide sur les N premières lignes ajoutées tandis que des données sont ajoutées dans les autres cellules des N lignes.

[Philippe Tulliez]

Bonjour,
Le filtre s'applique à tout la plage et l'utilisation de la propriété Resize ne change rien à l'affaire
Pour t'en convaincre, utilise la méthode AutoFilter sur la cellule A1

Code :

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

ActiveSheet.Range("$A$1").AutoFilter Field:=3, Criteria1:=Array( "Achat 1", "Achat 2", "Achat 3"), Operator:=xlFilterValues

en revanche, si tu utilises la méthode AdvancedFilter (filtre avancé d'excel), le filtre n'agit que sur la plage définie

Code :

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

 Range("A1:I11").AdvancedFilter xlFilterInPlace, Range("L1:L2")

A lire éventuellement Les filtres avancés ou élaborés dans Excel

[informer]

Bonjour Philippe,

Merci beaucoup pour cette info et surtout le lien vers ton excelllent tuto sur les filtres avancés

Dans mon code je fais un filtre sur plusieurs colonnes dont la valeur est donnée par un champ du recordset. Pourrais tu me donner un exemple car je ne vois pas comment affecter les valeurs de critère directement en VBA.

[Philippe Tulliez]

Bonjour,

Dans mon code je fais un filtre sur plusieurs colonnes dont la valeur est donnée par un champ du recordset. Pourrais tu me donner un exemple car je ne vois pas comment affecter les valeurs de critère directement en VBA.

Je préfère que tu lises d'abord le tutoriel car il y a un classeur à télécharger avec des exemples.
Il est parfaitement possible de définir les critères sans passer par le VBA. Si tu as déjà travaillé avec Access, la zone des critères ressemble à son éditeur de requête
Tu verras qu'après la lecture, tu prendras plaisir à utiliser cette méthode, si simple à mettre en place et qui offre moult possibilités

[informer]

Philippe,

J’ai téléchargé ton tuto mais les exemples ne me permettent pas de comprendre comment faire un filtre avancé.

Voici le contexte de mon projet:
Un fichier Excel est Maj avec des données d’une requête Access. Comme ma version d’Access ne permet pas de Maj de fichier Excel par SQL voir post Ici, je suis actuelllement obligé de Maj le fichier Excel sequentiellement.

J’ai développé dans ACCESS des routines VBA avec des objets DAO pour MAJ des fichiers Excel. Le principe général est de lire sequentiellement un recordset Access et de trouver la ligne correspondante dans Le fichier Excel par rapport à la valeur de plusieurs champs du recordset. L’autofilter est donc appliqué sur plusieurs colonnes du fichier Excel

L’autofilter fonctionne très bien à la marge de ce problème de sélection sur 1047585 lignes excel si la valeur de l‘autofilter est égale à blanc ("=") et appliqué sur une colonne sans données

Dans ce contexte je ne vois pas comment basculer sur le filtre avancé.

Merci de me mettre sur la voie car le téléchargement de ton tuto ne charge qu’un pdf sauf si quelque chose m'a échappé

[informer]

Sur la page The Ultimate Guide to Excel Filters with VBA Macros – AutoFilter Method

On peut lire le code suivant qui semble répondre parfaitement à mon problème

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub AutoFilter_Range()
'AutoFilter is a member of the Range object
 
  'Reference the entire range that the filters are applied to
  'AutoFilter turns filters on/off when no parameters are specified.
  Sheet1.Range("B3:G1000").AutoFilter
 
  'Fully qualified reference starting at Workbook level
  ThisWorkbook.Worksheets("AutoFilter Guide").Range("B3:G1000").AutoFilter
 
End Sub

La lecture de How to use AutoFilters in Excel VBA Macros, il faut d’abord désactiver désactiver les deux états suivants

Code :

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

AutoFilterMode = False And .FilterMode = False

D’où le code entier

Code :

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

1
2
3
4
5
6
7
8
9
Sub AutoFilter_Range()
 
 With ActiveSheet
   .FilterMode = False
   .AutoFilterMode = False 
 
  .Range("B3:G1000").AutoFilter
 End with
End sub

Bon ça c’est la théorie mais maintenant je dois le tester pour passer en résolu.

[Philippe Tulliez]

Bonjour,

J’ai téléchargé ton tuto mais les exemples ne me permettent pas de comprendre comment faire un filtre avancé.

As-tu vraiment lu ce tutoriel ?
Le filtre avancé est basé sur une zone de données et une zone des critères et tout est expliqué dans le tutoriel et le fichier exemple est téléchargeable au chapitre XIV. Fichier exemple

Alors tu viens avec un tas d'explications d'Access de DAO mais moi je n'ai fait que répondre à un problème que tu disais avoir avec la méthode AutoFilter où malgré l'utilisation de la propriété Resize le filtre se faisait sur l'ensemble de la liste. Ce à quoi j'ai répondu que la méthode AdvancedFilter pouvait filtrer une partie de Range. Point à la ligne.

[informer]

Bonjour Philippe,

Effectivement je suis passé à côté de ce chapitre.

Si je ne sers pas de la méthode de filtre avancé pour cette fois, grâce à toi, mon champ de connaissance s’est encore étendu et je suis persuadé que cela me servira dans le futur.

Encore merci Philippe pour ton aide, ton tuto et l’exemple à télécharger

[informer]

J'ai attaché un fichier xlsm à télécharger qui teste l'autofilter sur une plage spécifiée.

le code est le suivant

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
Private Sub CommandButton1_Click()
 
 
With ActiveSheet
   .FilterMode = False
   .AutoFilterMode = False
 
    Set oRange = .Range("A2:E8")
    With oRange
        .AutoFilter Field:=5, Criteria1:="=" 
    End With
 
 End With
 
End Sub

L'autofilter est bien appliqué sur la plage spécifiée avec message en bas à gauche --> 6 enregistrements(s) trouvé(s) sur 6"

[patricktoulon]

Bonjour
j'ai cru comprendre que le demandeur cherchait a determiner une plage a filtrer avec un resize afin peut etre de ne pas filtrer tout le sheets si il est remplie plus loin que la plage désirée

voila un en exemple qui determine la plage a filtrer de la colonne A à E a partir de "A2"

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub test2()
With Sheets(1)
nextrow = .Range("A:E").Find("*", , , , , xlPrevious).Row
Set plageafiltrer = .Range("A2").Resize(nextrow - .Range("A2").Row + 1, .Cells(1, "E").Column)
MsgBox plageafiltrer.Address
End With
with plageafiltrer
'.autofilter.......
'....
'.....
end with 
End Sub

a noter que dans cet exemple ou les cellule de la plage sont bien remplies le currentregion aurait suffit

[Philippe Tulliez]

Bonjour,
Nous n'avons sans doute pas la même notion de ce qu'est une plage partielle ou en tous les cas je n'ai pas compris la question

Exemple d'une liste de données comprise entre A1;E14 et dont nous ne voulons appliquer le filtre que sur A1:E8 (pour moi c'est ce que je comprends par une plage partielle)
Le critère étant d'appliquer le filtre sur cette plage partielle si la cellule de la colonne E est vide
La zone des critères (G1:G2) pour l'exemple avec AdvancedFilter est entourée en rouge

Pour la bonne compréhension si mes explications ne sont pas claires
En jaune : La plage partielle sur laquelle, je souhaite appliquer le filtre
En bleu : Les lignes dont la cellule de la colonne E est vide

On pourra observer que la méthode AutoFilter ne peut appliquer le filtre sur une plage partielle alors que la méthode AdvancedFilter le peut



Résultat du filtre avec la méthode AutoFilter
Code Source

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub TestAutoFilter()
 Dim oRange As Range
 With ActiveSheet
 .FilterMode = False
 .AutoFilterMode = False
  Set oRange = .Range("A1:E8")
  With oRange
  .AutoFilter Field:=5, Criteria1:="="
  End With
 End With
End Sub

Résultat avec la méthode AdvancedFilter
Code Source

Code :

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

1
2
3
4
5
6
7
8
9
Sub TestAdvancedFilter()
 Dim rngSource As Range
 Dim rngCriteria As Range
 With ActiveSheet
  Set rngSource = .Range("A1:E8")
  Set rngCriteria = .Range("G1:G2")
 End With
 rngSource.AdvancedFilter xlFilterInPlace, rngCriteria
End Sub

Maintenant si Patrick a raison sur la compréhension de la question, effectivement l'utilisation de la propriété CurrentRegion que j'utilise toujours pour sélectionner une liste de données est la solution.

Mais j'aurais posé la question autrement. Comment filtrer un tableau, une liste de données lorsque j'en ai plusieurs sur une feuille ?

[EDIT]
Mais je rappelle que le titre de la discussion est
Appliquer l’autoFilter sur un currentRegion.resize
donc l'initiateur de la discussion semble connaître les deux propriétés et je pense avoir bien compris la question telle qu'elle est posée

Exemple de la ligne 5 du dernier code
utilisant une adresse fixe

Code :

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

Set rngSource = .Range("A1:E8")

remplacé par cette ligne utilisant CurrentRegion.Resize

Code :

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

  Set rngSource = .Range("A1").CurrentRegion.Resize(8)

[informer]

Encore merci à tous pour vos réponses très constructives

A la lumière de tous ces échanges, je pense que dans mon code initial

Code :

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

1
2
3
4
5
6
7
8
lXlsRowNumber = .Cells(.Rows.Count, .Range("NomColRef").Column).End(xlUp).Row
Set oXlsRangeCurrentRegion = .Range("A1").CurrentRegion.Resize(RowSize:=lXlsRowNumber)
 With oXlsRangeCurrentRegion
                .AutoFilterMode = True
                lIdxCol = .Range("NomColCrit").Column 
                .AutoFilter Field:=lIdxCol, Criteria1:=IIf(Nz(oRecSet![Value], "") = "", "=", oRecSet![Value]) 
               Set xlsRangeAutoFilter = .SpecialCells(xlCellTypeVisible)
End With

mon problème vient de la ligne

Code :

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

.AutoFilterMode = True

Qui devrait être à false donc

Code :

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

.AutoFilterMode = False

[sultan87]

Bonjour
j'utilise filtre avancé dans un userform en vba
je voudrais trier le résultat

' liste des années d'adhésions

Sheets("Adhérents").Columns("I:I").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B10:C11"), CopyToRange:=Range("A10"), Unique:=True

pouvez vous m'aider
Pierre

[Nain porte koi]

Hello sultan87, créez un nouveau sujet, celui ci est résolu et date de 2019