XL 2013 Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

[Tranbi]

Bonjour,

Je souhaite faire un fichier, issu d'une extraction, permettant de supprimer les lignes en fonction de certains critères, et comme c'est un travail quotidien, je suis parti sur une macro.
Essayons de faire simple... donc, c'est là que ça se complique...

Pour commencer, je travaille sur la colonne événement qui se constitue de 2 codes de 3 lettres, ce qui rend les combinaisons très nombreuses. Je voudrais que ma macro supprime toutes les lignes ayant certaines combinaisons de code (CORFAC, CORFAE, ODR, RBT, INFCHC...) dans la colonne événement (voir fichier en pièce jointe).

J'ai bien réussi à supprimer certaines lignes mais je n'arrive pas à le faire pour plus de 2 critères.

ActiveSheet.Range("$A$1:$L$1000").AutoFilter Field:=2, Criteria1:="<>CORFAE" _
, Operator:=xlAnd, Criteria2:="<>ODR"

Mais si je rajoute ", Operator:=xlAnd, Criteria3:="<>CORFAC" "ça ne fonctionne pas

J'ai bien vu qu'on pouvait sélectionner avec le filtre l'ensemble des possibilités avec la fonction Array mais justement le nombre de combinaisons possibles est trop élevé et ne sort pas forcément tous les jours, alors que je n'ai qu'une dizaine de combinaisons à supprimer.

J'ai cherché sur différents forums mais je n'ai pas trouvé la solution, ou alors je n'ai pas compris ce qui se disait... étant novice je ne peux pas tout comprendre du premier coup...

Pour finir, une fois que j'ai obtenu la liste voulue, j'ai réparti les lignes en fonction de l'expéditeur (un onglet par expéditeur) dans des onglets afin d'imprimer chaque liste. Je voudrais imprimer uniquement les feuilles expéditeurs contenant des informations. Celles qui n'ont que la ligne 1 ne doivent pas s'imprimer.

Merci d'avance pour votre aide

 

[cathodique]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

Bonsoir,

J'espère pouvoir t'aider jusqu'au bout, sinon j'espère que quelqu'un d'autre prendra le relais.

Commençons à simplifier ton code pour la copie de ta base.

' Je commence par faire une copie des données avant traitement afin d'en garder une trace

Sheets("BASE").UsedRange.Copy Sheets("DONNEES").Range("A1") 'en une seule ligne

'___au lieu des lignes ci-dessous____________________________
 'Sheets("BASE").Select copy sheets("base")
'    Range("A1").Select
'    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'    Selection.Copy
'    Sheets("DONNEES").Select
'    Range("A1").Select
'    ActiveSheet.Paste
'    Range("A1").Select
'    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'    Application.CutCopyMode = False
'    Selection.AutoFilter
'    Cells.Select
'    Cells.EntireColumn.AutoFit
'_________________________________________________________________

Je n'ai pas compris comment tu fais pour tes différents critères, sur quelle base tu les choisis?
De plus tu as mis des points de suspension, ce qui veut dire que tes critères sont variables.

(CORFAC, CORFAE, ODR, RBT, INFCHC...)

à+

 

[klin89]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

Bonsoir cathodique, Tranbi, le forum

Pour supprimer, vois ceci :

Option Explicit

Sub Supprime_ref()
Dim ref As String
    ref = Chr(34) & Join$(Array("CORFAC", "CORFAE", "ODR", "RBT", _
                       "INFCHC"), Chr(34) & "," & Chr(34)) & Chr(34)
    With Sheets("Base")
        With .Range("b2", .Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
            .Formula = "=if(or(b2={" & ref & "}),1,"""")"
            On Error Resume Next
            .SpecialCells(-4123, 1).EntireRow.Select 'sélectionne
            '.SpecialCells(-4123, 1).EntireRow.Delete 'supprime
        End With
    End With
End Sub

Je n'ai pas compris la 2ème partie de la question, tu parles d'expéditeurs, je n'en vois point
klin89

 

[cathodique]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

Pour la première partie, je me suis inspiré d'un fichier de Boisgontier
sur la feuille menu, tu cliques sur "traitement des données", un userform va s'ouvrir, tu cliques sur la toupie et tu sélectionnes, chaque sélection va s'ajouter dans le champ de la combobox (séparées par ":"). puis en cliquant sur le bouton "supprimer" toutes les lignes correspondantes aux sélections en colonne A vont être supprimées. enfin si j'ai bien compris tes besoins.

Pour le code, je n'ai rajouter que celui pour éliminer les valeurs en double dans la combobox et le code pour supprimer les lignes. Tout le reste est de Boisgontier, qui a fait un formidable travail. C'est ma principale référence.

 

[Tranbi]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

Bonjour Cathodique,

Merci pour ta réponse... Il a juste fallu que je rajoute la ligne me plaçant sur la feuille "DONNEES" et ça roule (sinon Erreur d’exécution ‘1004’)
Concernant les critères, ce sont des codes INOVERT (normes de standardisation des échanges informatiques). Je veux supprimer ceux qui ne nécessitent plus de traitement de ma part. Donc, ils n'apparaissent pas forcément tous les jours et c'est pour ça que je veux les lister dans leur totalité pour ne pas en oublier et qu'ils soient supprimés automatiquement. Il y en a une petite dizaine au total. Effectivement, ces codes sont variables puisque les 3 premières lettres correspondent à un événement et les 3 dernières à la justification de cet événement, par exemple INFCHC = INF pour INFORMATION et CHC pour CHANGEMENT DE COMPTE. Ce sont des lignes que je n’ai pas besoin de traiter et par conséquent je veux les supprimer.
En ce qui concerne le userform, je ne trouve pas pratique le fait de devoir sélectionner les événements. Je préférerais que cela soit automatique. De plus, j’ai constaté qu’il fallait cliquer plusieurs fois sur « Supprimer » pour réussir à supprimer toutes les lignes d’un même événement.

Merci encore pour ta contribution

 

[Tranbi]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

Bonjour Klin89,
Tout d’abord merci pour ta réponse, mais je ne sais pas trop où placer ce code. J’ai lancé la macro mais tout ce que j’obtiens, c’est un « 1 » qui s’affiche dans la colonne A de ma feuille « BASE ». Je ne voudrais pas toucher aux données de la feuille « BASE », et donc il faudrait que ce code agisse sur la feuille « DONNEES ». J’imagine que cela dépend de l’endroit où j’intègre le code. Ensuite j’imagine qu’il faut lancer une suppression des lignes dont la cellule A contient un 1. Est-ce que je le fais via un filtre, ou alors il existe un code qui effectue cette opération ?

Enfin, concernant les expéditeurs, je confirme que ce n’est pas clair, puisque j’ai renommé mes onglets dans l’ordre alphabétique alors que j’ai laissé le code expéditeur (C.exp. dans la colonne I). Donc il n’y a pas de correspondance dans ce fichier (il y en a une dans le fichier officiel). Attention, il peut y avoir plusieurs codes expéditeurs pour un même onglet (exemple onglet A : 6632, 6706 et 6417).
Et donc, pour cette étape, une fois que les doublons ont été supprimés, ainsi que les événements inappropriés, je voudrais pouvoir dispatcher chaque ligne en fonction de son code expéditeur (colonne I) dans l’onglet correspondant et imprimer uniquement les feuilles (de A à Q) ayant des informations autres que la 1ère ligne.

Merci encore de ton aide

 

[klin89]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

Re tranbi,

Pour répondre au post #6, concernant le dispatch de tes données dans les bons onglets, on peut utiliser le filtre automatique.

Option Explicit

Sub Ventile()
Dim e, v, Dercell As Range
    Application.ScreenUpdating = False
    With Sheets("DONNEES").Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        'exemple onglet A : 6632, 6706 et 6417  ...etc
        For Each e In Array(Array(Array(6632, 6706, 6417), "a"), Array(Array(7618, 7619, 7936), "b") _
          , Array(Array(5078), "c"), Array(Array(6784), "d"), Array(Array(5415, 5651), "e") _
          , Array(Array(6001), "f"), Array(Array(6722), "g"), Array(Array(5408, 6749, 6751), "h") _
          , Array(Array(0), "i"), Array(Array(0), "j"), Array(Array(0), "k") _
          , Array(Array(7567), "l"), Array(Array(6658), "m"), Array(Array(7149), "n") _
          , Array(Array(0), "o"), Array(Array(0), "p"), Array(Array(7526), "q"))
            With Sheets(e(1)).Cells(1).CurrentRegion
                With .Offset(1)
                    .ClearContents
                End With
            End With
            For Each v In e(0)
                .AutoFilter 9, v
                Set Dercell = Sheets(e(1)).Range("b" & Rows.Count).End(xlUp)(2)
                .Offset(1).Copy Dercell.Offset(, -1)
                .AutoFilter
            Next
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Pour bien comprendre, à la 1ère itération :

e = Array(Array(6632, 6706, 6417), "a")
e(0) = Array(6632, 6706, 6417)
e(0)(0) = 6632
e(0)(1) = 6706
e(0)(2) = 6417
e(1) = "a"

klin89

 

[cathodique]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

Bonjour Tranbi,

Désolé de répondre tardivement, en fait j'avais oublié de cocher la case pour être averti par mail.

Sur ton fichier que j'ai repris ça fonctionne bien.

De plus, j’ai constaté qu’il fallait cliquer plusieurs fois sur « Supprimer » pour réussir à supprimer toutes les lignes d’un même événement.

Non,tu ne cliques pas plusieurs fois sur le bouton "SUPPRIMER" mais plutôt tu cliques plusieurs fois sur la toupie de la combo pour sélectionner tes items (qui vont se rajouter dans la combo) ensuite tu cliques une seule et unique fois pour que toutes les lignes des items que tu as sélectionné soient supprimées.

Mais bon, c'est une proposition. Les goûts et les couleurs ne se discutent pas, n'est-ce pas?

Pour les feuilles, j'avais compris que tu faisais une copie de ta feuille "BASE" dans la feuille "DONNEES", avant de supprimer les lignes dont tu n'as pas besoin dans la feuille "BASE".

nb: si tu resélectionnes une seconde fois un item, il est retiré de la combo. pour info

Bonne continuation.

 

[cathodique]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

Re,

Je reviens pour te demander si tu connais au préalable tous les codes que tu dois supprimer de ta feuille "DONNEES".

Si c'est le cas le code peut-être modifié pour supprimer les lignes sans passer par l'userform de sélection des codes.

Donne-nous la liste des codes à supprimer.

 

[cathodique]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

si tu connais tes codes

Sub TRAITEMENT()
' Je commence par faire une copie des données avant traitement afin d'en garder une trace
    Application.ScreenUpdating = False
    Sheets("BASE").UsedRange.Copy Sheets("DONNEES").Range("A1")

    Dim Code()
    Dim i As Integer

    'Alimente les éléments du tableau code
    Code = Array("CORFAC", "CORFAE", "RBT", "ODR", "INFCHC")
    
    'Boucle sur les éléments du tableau pour lire leur contenu
    With Sheets("DONNEES")
        dl2 = .Range("B" & Rows.Count).End(xlUp).Row
        For i = LBound(Code) To UBound(Code)

            For j = 1 To dl2
                If .Range("B" & j).Value = Code(i) Then .Rows(j).Delete
            Next j
        Next i
        'nb lignes après suppression
        dl2 = .Range("B" & Rows.Count).End(xlUp).Row
    End With


    With Sheets("BASE")
        dl1 = .Range("B" & Rows.Count).End(xlUp).Row
    End With

    MsgBox "Il y avait dans la feuille Base " & dl1 - 1 & " lignes" & vbLf & vbLf & "Sur la feuille DONNEES, il y a " _
         & dl2 - 1 & " lignes" & vbLf & vbLf & "Soient " & dl1 - dl2 & " lignes supprimées"

    Application.ScreenUpdating = True

End Sub

 

[Tranbi]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

Bonjour,

J'ai un peu mis de côté mon fichier en raison du boulot, d'où l'absence de réponse de ma part. Voici la liste des codes à supprimer automatiquement : CORFAE, CORFAC, RBT, RGL, INFCHC, LITCOU, SOLREI, SOLRLV, SOLENL, SOLANN.

Par contre, je dois t'avouer que, ne maîtrisant pas le langage VBA, je ne comprends pas les modifications que tu apportes et surtout à quel endroit intégrer le code et quelle partie doit être remplacée... Il faut que je trouve un cours sur le VBA qui me permettra de comprendre le tout... si tu as des suggestions, ça m'intéresse.

Merci encore

 

[cathodique]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

Bonjour,

Primo, est-ce que tu as effectué des tests avec l'userform (plutôt as-tu compris comment ça fonctionne). On clique plusieurs fois sur la toupie pour effectuer les items et à la fin on clique sur le bouton supprimer.

Sinon avec ce code

Sub TRAITEMENT()
' Je commence par faire une copie des données avant traitement afin d'en garder une trace
   Application.ScreenUpdating = False
    Sheets("BASE").UsedRange.Copy Sheets("DONNEES").Range("A1")

    Dim Code()
    Dim i As Integer
'tes codes :CORFAE, CORFAC, RBT, RGL, INFCHC, LITCOU, SOLREI, SOLRLV, SOLENL, SOLANN
    'Alimente les éléments du tableau code
   Code = Array("CORFAC", "CORFAE", "RBT", "RGL", "INFCHC","LITCOU", "SOLREI", "SOLRLV", "SOLENL", "SOLANN")
   
    'Boucle sur les éléments du tableau pour lire leur contenu
   With Sheets("DONNEES")
        dl2 = .Range("B" & Rows.Count).End(xlUp).Row
        For i = LBound(Code) To UBound(Code)

            For j = 1 To dl2
                If .Range("B" & j).Value = Code(i) Then .Rows(j).Delete
            Next j
        Next i
        'nb lignes après suppression
       dl2 = .Range("B" & Rows.Count).End(xlUp).Row
    End With


    With Sheets("BASE")
        dl1 = .Range("B" & Rows.Count).End(xlUp).Row
    End With

    MsgBox "Il y avait dans la feuille Base " & dl1 - 1 & " lignes" & vbLf & vbLf & "Sur la feuille DONNEES, il y a " _
         & dl2 - 1 & " lignes" & vbLf & vbLf & "Soient " & dl1 - dl2 & " lignes supprimées"

    Application.ScreenUpdating = True 
End Sub

Tu fais Alt+F11, l'éditeur VBE s'ouvre, tu vas sur le menu "insertion" et là tu cliques sur module ensuite tu fais un copier/coller du code à partir du forum. et ensuite tu l'éxécutes (mets le curseur sur n'imoprte quelle ligne et appuie sur F5.

Personnellement, j'ai commencé par l'enregistreur de macro, mais le code générer n'est pas le top du codage.

Mais j'ai beaucoup appris de ce site de Boisgontier et sur ce forum.

Bonne continuation

 

[Tranbi]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

Bonjour Cathodique,

Donc j'ai procédé selon les étapes que tu m'indiquais. J'ai copié/collé le code dans la fenêtre VBA, j'ai appuyé sur F5 et ça a supprimé des lignes... mais pas la totalité. Certaines lignes, par exemple, avec le code SOLANN ont disparu mais il en reste encore.
Pour repartir de zéro, j'ai ouvert un nouveau fichier en gardant uniquement les onglets BASE et DONNEES et j'ai utilisé ton code. Problème : ça ne supprime aucune ligne. Je me permets donc de mettre le nouveau fichier, épuré au maximum (plus de menu, plus de feuilles de ventilation...).
J'ai également essayé avec le userform qui permet de sélectionner les événements à supprimer, et je maintiens que ça ne supprime pas toutes les lignes.
Dans le fichier joint, l'onglet BASE contient 292 lignes et après traitement (suppression des événements + suppression des doublons) il me reste 174 lignes alors que normalement, il ne devrait m'en rester que 150.

Je dois avouer que la solution du userform ne me satisfait pas plus que ça car je ne suis pas sûr de ne pas passer à côté d'un événement par mégarde.
Je préfère la solution avec le code VBA mais il faudrait que tous les événements soit supprimés, ce qui n'est pas le cas.

Sub TRAITEMENT()
' Je commence par faire une copie des données avant traitement afin d'en garder une trace
  Application.ScreenUpdating = False
    Sheets("BASE").UsedRange.Copy Sheets("DONNEES").Range("A1")

    Dim Code()
    Dim i As Integer
'tes codes :CORFAE, CORFAC, RBT, RGL, INFCHC, LITCOU, SOLREI, SOLRLV, SOLENL, SOLANN
   'Alimente les éléments du tableau code
  Code = Array("CORFAC", "CORFAE", "RBT", "RGL", "INFCHC","LITCOU", "SOLREI", "SOLRLV", "SOLENL", "SOLANN")
   
    'Boucle sur les éléments du tableau pour lire leur contenu
  With Sheets("DONNEES")
        dl2 = .Range("B" & Rows.Count).End(xlUp).Row
        For i = LBound(Code) To UBound(Code)

            For j = 1 To dl2
                If .Range("B" & j).Value = Code(i) Then .Rows(j).Delete
            Next j
        Next i
        'nb lignes après suppression
      dl2 = .Range("B" & Rows.Count).End(xlUp).Row
    End With


    With Sheets("BASE")
        dl1 = .Range("B" & Rows.Count).End(xlUp).Row
    End With

    MsgBox "Il y avait dans la feuille Base " & dl1 - 1 & " lignes" & vbLf & vbLf & "Sur la feuille DONNEES, il y a " _
         & dl2 - 1 & " lignes" & vbLf & vbLf & "Soient " & dl1 - dl2 & " lignes supprimées"

    Application.ScreenUpdating = True 
End Sub

Enfin, il me semble que tu n'as pas abordé le problème des doublons. Je voudrais reprendre la ligne

ActiveSheet.Range("$A$1:$L$1000").RemoveDuplicates Columns:=3, Header:= _
        xlYes

mais j'ai un gros doute sur l'endroit où la placer dans le code.

Merci et bonne soirée

 

[cathodique]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

j'ai appuyé sur F5 et ça a supprimé des lignes... mais pas la totalité. Certaines lignes, par exemple, avec le code SOLANN ont disparu mais il en reste encore.

Désolé, il n'y a pas de raison que certaines lignes ne soient pas supprimées si le code est le même, à moins que pour les lignes non supprimées le code ne soit pas exactement le même. il se pourrait que tu aies des caractères non visibles, tels que des espaces.

For j = 1 To dl2
                'If .Range("B" & j).Value = Code(i) Then .Rows(j).Delete 'remplace cette ligne par celle du dessous
                 If Trim(.Range("B" & j).Value) = Code(i) Then .Rows(j).Delete
            Next j

La fonction Trim supprime les espaces avant et après une chaine de caractères
Pour les doublons tu n'en avais pas parlé.

 

[cathodique]

Re : Supprimer les lignes qui résultent d'un filtre multicritères avec une macro

En fin de compte,tu avais raison j'ai mal testé. Essaie ce code.
Mais une question pourquoi tes données commencent en colonne B?

Sub Supprimer_Ligne_Code()
    Dim Code(), j As Long
    Application.ScreenUpdating = False
    Sheets("BASE").UsedRange.Copy Sheets("DONNEES").Range("B1")
    Code = Array("CORFAC", "CORFAE", "RBT", "RGL", "INFCHC", "LITCOU", "SOLREI", "SOLRLV", "SOLENL", "SOLANN")
    With Sheets("donnees")
        For j = .Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
            If Not IsError(Application.Match(Cells(j, 2).Text, Code, 0)) Then .Rows(j).Delete
        Next j
    End With
    Application.ScreenUpdating = False
End Sub