Discussion :

[counterbob]

Bonjour
lorsque je définis la plage de critères pour un filtre avancé j'ai une erreur sur cette ligne de code.
erreur 1004 la méthode AdvancedFilter de la classe Range a échoué.

Code :

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

1
2
3
4
Dim dercel As String
  dercel = Range("M65000").End(xlUp).Address(RowAbsolute:=False, ColumnAbsolute:=False)
  Range("M1") = "Grade"
  [A1:H1000].AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=[M1:dercel]'Ligne en erreur

C'est le dercel qui coince, je n'arrive pas à définir le critère variable dans la plage.

J'ai lu le Tuto de Philippe Tulliez
mais je coince

Merci pour une piste
Cordialement

[Philippe Tulliez]

Bonjour,

J'ai lu le Tuto de Philippe Tulliez
mais je coince

Cela fait toujours plaisir d'avoir des lecteurs et merci de le signaler

En revanche, je ne référence jamais les plages de cellules de la façon dont vous le faites (entre crochet) et à fortiori dans ce tutoriel.
C'est une très mauvaise pratique utilisée qui n'a que des désavantages. Vous venez d'ailleurs d'en faire les frais
Je vous invite à lire le billet de Pierre Fauconnier sur ce sujet VBA-Excel: Notation raccourcie d'une plage, une fausse bonne idée!

Revenons maintenant à votre code

Pour votre deuxième ligne de code dercel = Range("M65000").End(xlUp).Address(RowAbsolute:=False, ColumnAbsolute:=False)
Partir de la ligne 65000 alors que depuis la version 2007 (plus de 15 ans) la feuille d'un classeur excel normal (suffixé xlsx, xlsm, xlsb, etc.) possède 1.048.576 lignes ce n'est pas sérieux.
Je ne comprends pas la raison pour laquelle vous écrivez à la cellule M1 le texte Grade alors qu'en même temps vous cherchez la dernière ligne remplie de cette colonne

Il serait intéressant de savoir ce que vous cherchez à faire.
Soit faire une filtre sur place avec une zone des critères déjà définie et alors il suffit de la nommer et utiliser ce nom dans le programme soit les critères sont construits dynamiquement par le programme et alors on dit travailler autrement (par exemple avec la propriété CurrentRegion)

Pour définir la zone des données, j'utilise toujours la propriété CurrentRegion ou dans 99% des cas comme j'utilise des tableaux structurés, je travaille avec l'objet Range de l'objet ListObject

Exemple avec la première hypothèse (la zone est fixée et nommée areaCriteria) et la plage traitée est une plage qui n'est pas un tableau structuré

Code :

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

1
2
3
4
5
6
Sub tata()
  Dim rngData As Range
  Set rngData = ThisWorkbook.Worksheets("Prestation").Range("A1").CurrentRegion
  rngData.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("areaCriteria")
  Set rngData = Nothing
End Sub

[counterbob]

Bonjour,


Il serait intéressant de savoir ce que vous cherchez à faire.
Soit faire une filtre sur place avec une zone des critères déjà définie et alors il suffit de la nommer et utiliser ce nom dans le programme soit les critères sont construits dynamiquement par le programme et alors on dit travailler autrement (par exemple avec la propriété CurrentRegion)

Pour définir la zone des données, j'utilise toujours la propriété CurrentRegion ou dans 99% des cas comme j'utilise des tableaux structurés, je travaille avec l'objet Range de l'objet ListObject

Exemple avec la première hypothèse (la zone est fixée et nommée areaCriteria) et la plage traitée est une plage qui n'est pas un tableau structuré

Code :

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

1
2
3
4
5
6
Sub tata()
  Dim rngData As Range
  Set rngData = ThisWorkbook.Worksheets("Prestation").Range("A1").CurrentRegion
  rngData.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("areaCriteria")
  Set rngData = Nothing
End Sub

Bonjour

Merci pour ces conseils.

Exemple avec la première hypothèse (la zone est fixée et nommée areaCriteria) et la plage traitée est une plage qui n'est pas un tableau structuré

Si je comprends bien, j'ai fixée et nommée la zone, ici la zone de critères se situe sur une feuille"Parametres" Cellule B20:B25. je l'ai nommée ZoneFilt1 elle contient les valeurs MED1,MED2,INF1,INF2

Code :

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

1
2
Set rngData = ThisWorkbook.Worksheets("GARD1CHU").Range("A1").CurrentRegion
  rngData.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("ZoneFilt1")

Hélas rien ne se passe ou plutôt ça vide tout.

je mets le bout de code qui fonctionne.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
With Sheets("GARD1CHU")
   'conserve uniquement AS1, AS2 et AH 
  [triGard1].Copy Destination:=Sheets("GARD1CHU").Range("M2")'copie les valeurs de la plage "triGard1" vers les cellules M2,M3,M4 etc ...(plage variable)
  Dim dercel As String
  dercel = Range("M65000").End(xlUp).Address(RowAbsolute:=False, ColumnAbsolute:=False)'définit la dernière valeur de la plage M1:Mx
  Range("M1") = "Grade" 'Peut-être inutile ?
  [A1:H1000].AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=[M1:M9]'effectue le filtre élaboré . c'est ici que je souhaiterais le nom de la plage nommée
  On Error Resume Next
  Range("_FilterDataBase").Offset(1, 0).Resize(Range("_FilterDataBase"). _
      Rows.Count - 1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp 'supprime les valeurs
    ActiveSheet.ShowAllData 'affiche les valeurs restantes

Voilà je ne sais pas si c'est plus clair.

Merci
@+
Cordialement

[Philippe Tulliez]

Bonjour,

je mets le bout de code qui fonctionne.

Et bien si le code que vous avez publié fonctionne comme vous le dites, tout va bien.
Vous n'avez donc plus besoin d'aide et vous pouvez donc mettre le statut comme résolu

[Pierre Fauconnier]

Salut.

Il faudrait préciser votre version d'Excel. Depuis 2010, Power Query remplace avantageusement les filtres avancés en VBA. Il est plus que temps à mon sens que cet outil soit connu et utilisé.

[counterbob]

Salut.

Il faudrait préciser votre version d'Excel. Depuis 2010, Power Query remplace avantageusement les filtres avancés en VBA. Il est plus que temps à mon sens que cet outil soit connu et utilisé.

Bonsoir
La version est 2016 et 2019

[Pierre Fauconnier]

Salut.

Comme le dit Philippe (Hello ), remonter à partir de la ligne 65000 témoigne d'un vieux code qui valait jusqu'à la version 2003, donc en gros jusqu'en 2007.

Depuis, les tableaux structurés ont fait leur apparition, et je ne conçois pas avoir besoin des filtres avancés sur une plage classique alors que cela fait 13 ans que les tableaux structurés ou tables de données existent.

Avec 2016 et 2019, il faut à mon sens utiliser POWER QUERY pour remplacer les filtres avancés qui ont (mal) vieilli et sont à mon sens obsolètes, à de très rares exceptions près. Une recherche "Power Query" sur le forum va te donner une foultitude d'idées et d'exemples...

Bonne découverte et bon travail avec cet outil incontournable d'Excel

[counterbob]

Salut.

Comme le dit Philippe (Hello ), remonter à partir de la ligne 65000 témoigne d'un vieux code qui valait jusqu'à la version 2003, donc en gros jusqu'en 2007.

Depuis, les tableaux structurés ont fait leur apparition, et je ne conçois pas avoir besoin des filtres avancés sur une plage classique alors que cela fait 13 ans que les tableaux structurés ou tables de données existent.

Avec 2016 et 2019, il faut à mon sens utiliser POWER QUERY pour remplacer les filtres avancés qui ont (mal) vieilli et sont à mon sens obsolètes, à de très rares exceptions près. Une recherche "Power Query" sur le forum va te donner une foultitude d'idées et d'exemples...

Bonne découverte et bon travail avec cet outil incontournable d'Excel

Merci pour ces excellents conseils.
J'ai pris le chemin de Power Query et effectivement quelle avancée dans les recherches croisées, extractions etc !
merci encore.
Bien cordialement.

[Pierre Fauconnier]

Salut.

J'ai écrit un billet de blog sur les différentes jointures possibles en Power Query...