Filtre élaboré et ligne des entêtes

Version imprimable

16/01/2020, 16h00
elclor

Filtre élaboré et ligne des entêtes

Bonjour
Quand on applique un filtre élaboré à une BD avec copie dans une autre feuille la ligne des entêtes est aussi recopiée. Existe-t-il une instruction pour l'empêcher?
Merci de votre aide.
16/01/2020, 16h10
BENNASR

bonjour
peut tu montrer le code qui fait cette manipulation à savoir filtrer et copier ?
16/01/2020, 16h25
78chris

Bonjour

Le filtre élaboré ne copie les en-têtes que si tu n'as pas préparé la zone cible

Si tu prépares des en-têtes, seules les colonnes correspondantes sont extraites et non toutes les colonnes

Par construction le filtre élaboré a besoin des en-têtes. C'est une fonctionnalité de manipulation de tables et, comme dans une BD, les titres correspondent aux noms des champs de la table et sont donc indispensables

Sur 2016 plutôt qu'un filtre élaboré en VBA on utilise PowerQuery...

2 pièce(s) jointe(s)

Bonjour,

Il me semble curieux d'enlever les entêtes.

Exemple qui efface les entêtes après extraction.
Sur l'exemple c'est Rows(5).ClearContents qui efface les entêtes.

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
Private Sub Worksheet_selectionChange(ByVal Target As Range)
  If Target.Address = "$A$2" Then
    Set d = CreateObject("scripting.dictionary")
    temp = [Tableau1[Beneficiaire]]
    For Each c In temp
      d(c) = ""
    Next c
    If d.Count > 0 Then
      temp = Join(d.keys, ",")
      Target.Validation.Delete
      If temp <> "" Then Target.Validation.Add xlValidateList, Formula1:=temp
    End If
  End If
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$2" Then
    Sheets("Factures").[A1:H1000].AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=[A1:A2], CopyToRange:=[A5]
    Application.EnableEvents = False
    Rows(5).ClearContents
    Application.EnableEvents = True
  End If
End Sub

Pièce jointe 531154

Boisgontier

17/01/2020, 12h52
Pierre Fauconnier

Citation:

Envoyé par 78chris

[...]
Sur 2016 plutôt qu'un filtre élaboré en VBA on utilise PowerQuery...

Il faudra encore beaucoup taper sur le clou, je pense. C'est tout de même vachement plus simple que les codes proposés à coup de dictionnaire, d'événements et autres, sans déclarations de variables et avec des codes datant au moins de 2003, sans parler de la notation à crochets, qui plus est avec une limite fantaisiste de la plage alors que les tableaux structurés sont présents depuis 12 ans (Je ne compte pas les "listes de données" présentes dès 2003). Je ne comprends pas comment on en est encore là en 2020 ^^
18/01/2020, 10h25
elclor

Bonjour et merci à tous
pour 78Chris
J'avais bien préparé la feuille cible peut-être même trop notamment j'y avais prévu des sous-totaux et tout a été écrasé par la copie. Et de plus j'y avais placé les entêtes mais la copie les a répétées sue la ligne cible

pour boisgontierjacques
Comme tu le vois les entêtes se trouvaient déjà dans la feuille cible et je voulais éviter la répétition en les éliminant avant copie surtout que je voulais répéter plusieurs fois le filtrage

pour Pierre Fauconnier
Je ne connais rien du PQ mais tu m'as donné envie de commencer et pour cela j'essaye de refaire ton exemple.
Mais dès le début en ajoutant une colonne avec la condition identique à la tienne=[@PAYS]="AU" je reçois le message d'erreur suivant : la syntaxe de ce nom est incorrecte et ce qui est dans les crochets est mis en surbrillance. Une colonne PAYS existe bien dans ma BD et AU comme valeur d'une cellule de cette colonne. Peux-tu me dire où est mon erreur? Et peux-tu me conseiller de la lecture sur PQ?

Merci de votre aide passée, présente et future

1 pièce(s) jointe(s)

Lorsque je parle de la notation à crochets, je ne parle pas de références structurées, mais d'un code tel que [a1:a1000].End(xlUp). Cette syntaxe "d'initié" ( :ptdr: ) ne sert à rien, à part embrouiller, se priver de l'autocomplétion, demander plus de travail au compilateur (liaison tardive) et être source d'erreurs non détectables à la compilation, comme je l'explique dans mon billet. Tout ça pour ne pas saisir Range("a1:a1000").End(xlUp). Qui plus est, sauf lors de la phase de nettoyage et de préparation de données tierces extraites de façon barbare, un End(xlUp) a perdu beaucoup de son utilité si l'on conçoit correctement son classeur avec des tableaux structurés. Il n'est donc pas normal d'en avoir besoin pour un filtre avancé, puisque depuis 12 ans, on devrait appuyer le filtre avancé en VBA sur base d'une référence de tableau structuré, et pas en bornant la plage à coup de End(xlUp) (si on n'utilise pas PQ, ce qui à mon avis est une erreur depuis XL2010).

Cette notation n'a aucun rapport avec les références structurées (voir ce chapitre de mon tuto sur les tableaux structurés) qui a, quant à elle, une utilité certaine, tant en Excel qu'en VBA.

Basiquement, pour un filtre avancé, qui en 2020 s'appuie sur un tableau structuré, on a le code suivant, sur base de la configuration Excel suivante avec les données stockées dans la table t_Ventes, en voulant extraire uniquement les valeurs pour le client A...
Pièce jointe 531569
Code:

1 2 3 4 5 6 7 8 9 Sub Test() On Error GoTo EndHandler Application.DisplayAlerts = False Range("t_Ventes[#all]").AdvancedFilter xlFilterCopy, Range("e1:e2"), Range("g1:g2") EndHandler: Application.DisplayAlerts = True If Err <> 0 Then MsgBox Err.Description, vbExclamation, "Erreur: " & Err.Number End Sub
Par contre, je n'ai pas compris ta remarque portant sur le =[@PAYS]="AU". Peux-tu donner le contexte et un supplément d'explications?

Autre question: Pourquoi ne veux-tu pas les entêtes? Si c'est parce que tu utilises d'autres entêtes que ceux du tableau d'origine, il te suffit de les mémoriser puis de les restituer. Mais à nouveau, j'enfonce le clou: Avec Power Query, tout cela, même la modification d'entête, peut se réaliser sans vba.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Sub Test() On Error GoTo EndHandler Dim Headers Application.DisplayAlerts = False Headers = Range("g1:h1") ' On mémorise Range("g1:h1").Value = "" ' On efface Range("t_Ventes[#all]").AdvancedFilter xlFilterCopy, Range("e1:e2"), Range("g1:h1") Range("g1:h1").Value = Headers 'on restitue EndHandler: Application.DisplayAlerts = True If Err <> 0 Then MsgBox Err.Description, vbExclamation, "Erreur: " & Err.Number End Sub
Et au passage, fais attention que la plage d'arrivée n'est pas toujours nettoyée, ce qui peut laisser des crasses (voir le tuto de Philippe Tulliez au sujet des filtres avancés)

21/01/2020, 21h20
Pierre Fauconnier

Citation:

Envoyé par elclor

[...]
Mais dès le début en ajoutant une colonne avec la condition identique à la tienne=[@PAYS]="AU" je reçois le message d'erreur suivant[...]

Il n'y aurait pas un espace derrière PAYS dans l'intitulé de la colonne?