Discussion :

[jeo13]

Bonjour,

J'ai un classeur excel dans lequel j'aimerais trier les données d'un coup a partir du code vba sur les filtres automatiques.

Mon but est de faire afficher uniquement les données pour lesquelles 13 champs (colonnes F,G,H,I,N,O,Q,R,S,T,U,W,X) sont vides ou valent certaines valeur précises.

Je cherche depuis un moment mais je ne trouve pas comment exprimer la condition qui pourrait s'exprimer comme suit:
"Faire afficher les lignes pour lesquelles la valeur dans la colonne F est Vide OU la valeur dans la colonne G est Vide OU la valeur dans la colonne H est Vide OU la valeur dans la colonne I vaut 'Prospect' etc..."

Il s'agit bien de OU et non pas ET.


Merci d'avance !

[neupont]

Tu devrais chercher dans l'aide Excel (et non pas VBA) avec "Filtrer à l'aide de critères élaborés".

[ouskel'n'or]

Combien de critères ? Si plus de 3 pas de solution avec le filtre élaboré. Mais regarde ici, ça pourrait peut-être te convenir. Le problème des cellules vides n'en serait plus un

[jeo13]

Yes merci je suis tombé dessus lors de ma recherche tout a l'heure.
Mais en fait mon but est de faire afficher les lignes pour lesquelles certaines valeurs/colonnes sont vides ou valent certaines valeurs précises.

Donc je ne sais pas si les cellules vides sont réellement un probleme.

Je viens de tester la méthode ton topic avec le code 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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Sub FilterMultiCriteria()
Dim FL1 As Worksheet 'La feuille de calculs
Dim DerCol As String, NoCol As Integer
Dim Criteres As Variant, Colonnes As Variant
 
Dim Plage As Range 'utiles si Option de copie désirée
 
    '**************** LIGNES A ADAPTER à SON PROJET ********************************************
        'Les critères de recherche
        Criteres = Array("", "", "", "", "", "", "", "", "", "", "", "", "", "", "'Prospect' Or 'No risk Relation'")
        Colonnes = Array("", 5, 6, 7, 8, 9, 10, 14, 15, 17, 18, 19, 21, 23, 24)
 
        'Instance de la feuille de calculs "Feuil1"
        Set FL1 = Worksheets("CounterpartsStatistics")
    '*******************************************************************************************
 
        'Suppression d'un filtre existant éventuel
        If FL1.FilterMode = True Then FL1.Cells.AutoFilter
 
        'Dernière colonne de la plage de données
        DerCol = Split(FL1.Cells(1, Rows(1).Cells.Count).End(xlToLeft).Address, "$")(1)
 
    'Pose du filtre sur toutes les colonnes de la plage de données
    FL1.Columns("A:" & DerCol).AutoFilter
 
    'Filtrage des n colonnes
    For NoCol = 1 To UBound(Criteres)
        FL1.Cells(Colonnes(NoCol), Colonnes(NoCol)).CurrentRegion.AutoFilter _
                Colonnes(NoCol), Criteres(NoCol)
    Next NoCol
 
'    '******************** OPTION : Copy of the CounterpartsStatistics sheet on sheet 2 **********
'    Set Plage = FL1.Cells(1, 1).CurrentRegion                                                  '*
'    Plage.Copy Worksheets("Sheet2").Cells(1, 1)                                                '*
'    '********************************************************************************************
 
End Sub

Le code marche comme il faut mais je n'ai pas le résultat escompté vu que je n'ai plus aucune ligne en sortie...

alors que le but, est de détecter les lignes pour lesquelles certains champs/colonnes valent des valeurs données (valeur vides pour la plupart, ou du texte)

[ouskel'n'or]

Ce code est censé filtrer des cellules renseignées, pas des cellules vides.
Peux-tu mettre un exemple de ta base et un exemple de la base filtrée selon tes critères, qu'on y voit plus clair
A+

[jeo13]

Oui pas de probleme voici un fichier excel avec deux feuilles.
La premiere avec les données a trier.
La deuxieme feuille avec les données triées.

J'ai mis en rouge les noms de champs pour lesquels j'ai des criteres.
Et j'ai mis en cellulle grisée les cellulles correspondant a mes criteres, a savoir les cellules vides sauf pour la colonne V ou le critere est: 'No relation' ou 'Prospect'.

Et c'est bien un OU entre les criteres.
On garde la ligne des que l'un des criteres est vérifié sur l'une des colonnes ciblées.

[ouskel'n'or]

Si je comprends bien, tu veux simplement ne conserver que les lignes non complètement renseignées... Est-celà ?
A+

Cépaça puisque dans la ligne filtrée, ta colonne "Rating Last Revision Date" n'est pas renseignée.
Explique...
A+

[jeo13]

Effectivement c'est pas ca. Mes excuses si je n'ai pas été clair

Rating Last Revision Date n'est pas une donnée que je controle.
Comme je l'ai dit dans le post précédent, j'ai mis en rouge les champs (au nombre de 13) pour lesquels j'ai des criteres

Les criteres sont simples:
- Pour les 12 premiers champs (en rouge) les cellulles ne doivent pas etre vides. Dés qu'une de ces 12 cellules est vide on garde la ligne entiere.
- Pour le 13 eme champ ('TypeOfCommercial Relation Desc (L1)'), la valeur des cellules ne doit pas valoir 'Prospect' ou 'No relation'. Des que cette cellule vaut 'Prospect' ou 'No relation', idem on garde la ligne entiere.

Dans mon exemple seul une ligne est correcte dans le sens ou tous les champs obligatoires sont renseignés et donc ne figure plus dans la deuxieme feuille apres le tri.

[ouskel'n'or]

Il y a déjà une solution mais qui implique une voire deux boucles
Une pour examiner les lignes, la seconde pour vérifier ce qu'il y a dedans...
Long et fastidieux (pour VBA).
Je pense à une autre solution mais je dois la tester
Consiste à créer un tableau des cellules vide puis, en excluant les colonnes non concernées, à afficher les lignes ayant des cellules vides.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Test()
Dim Plage As Range, cell As Range
    NoCol = " 1, 2, 3, 4, 11, 14, 18, 20, 21, 23, "
    Set Plage = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
    Cells.EntireRow.Hidden = True
    For Each cell In Plage
        If InStr(NoCol, " " & cell.Column & ", ") = 0 Then
            cell.EntireRow.Hidden = False
        End If
    Next
    Columns("A:Z").EntireColumn.Hidden = False
    Range("A1").Select
    Rows(1).EntireRow.Hidden = False
End Sub

Testé, semble fonctionner
Pour le moment j'en suis là, si je pense à mieux, je te dis
A+

[jeo13]

Merci pour ton code.
Je viens de le tester mais il me donne une erreur sur NoCol qui n'a pas de type défini et peut etre une virgule en trop a la fin...

Et j'ai du mal a faire le lien entre ton code et tes explications

[ouskel'n'or]

Désolé, ajoute

Code :

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

Dim NoCol as string

Tu testes et si ça te va, je documenterai mon code.
A+

[jeo13]

Merci mais désormais il bloque sur la ligne en gras

Code :

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

1
2
3
4
   Dim Plage As Range, cell As Range, NoCol As String
    NoCol = " 1, 2, 3, 4, 11, 14, 18, 20, 21, 23, "
    Set Plage = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
    Cells.EntireRow.Hidden = True

Avec l'erreur suivante (traduite depuis l'anglais):

"impossible de mettre en place la Propriété Hidden pour Range"

[ouskel'n'or]

L'embêtent ces anglais !...
On va faire autrement. Remplace la ligne récalcitrante par

Code :

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

    Range("A1:A"& cells(columns(1).cells.count, 1).End(xlUp).Row).entirerow.hidden = True

Tu dis
A+

[jeo13]

Lol yes l'anglais parfois c'est rude

Alors la c'est bon ca fonctionne.
Mais je veux quand meme savoir comment ca marche parce que j'ai du mal a comprendre. Et je pense que je dois l'adapter car si NoCol représente les numéros de colonnes a surveiller, je dois en ajouter pour avoir les 13...

Merci !

[ouskel'n'or]

Ok

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
25
26
27
28
Sub Test()
Dim Plage As Range, cell As Range, NoCol as string
    'Liste des colonnes à ignorer composées de " " + No + "," + " "
    'ceci afin de ne pas confondre 1 avec 11, 2 avec 20, etc.
    NoCol = " 1, 2, 3, 4, 11, 14, 18, 20, 21, 23, "
 
    'Je crée l'instance de la plage de cellules vides. Dans ton exemple, donne
'Plage.address= "$F$2:$I$2,$M$2,$S$2,$N$2:$N$3,$G$4:$I$10,$U$5,$S$4:$S$7,$E$8,$F$4:$F$9,$M$9:$O$9,$P$9:$Q$10,$M$10:$N$10,$S$9:$S$10"
    Set Plage = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
 
    'ici je masque toutes les lignes renseignées
    Range("A1:A"& cells(columns(1).cells.count, 1).End(xlUp).Row).entirerow.hidden = True
    'Ici je fais une boucle sur les cellules vides de la plage et je vérifie que la
    'colonne n'appartient pas à NoCol (on aurait pu utiliser Intercept mais bon...)
    'j'ai fait comme çapour le principe, après on peut améliorer)
    For Each cell In Plage
        If InStr(NoCol, " " & cell.Column & ", ") = 0 Then
            'si la cellule n'est pas dans une colonne prohibée, on affiche la ligne
            cell.EntireRow.Hidden = False
        End If
    Next
    'la ligne suivante est inutile tu peux la supprimer
    'Columns("A:Z").EntireColumn.Hidden = False
    'uniquement pour que tu vois le résultat
    Range("A1").Select
    'comme j'ai masqué toutes les lignes, j'affiche la ligne d'entête
    Rows(1).EntireRow.Hidden = False
End Sub

Ce qui, une fois corrigé donne

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub Test()
Dim Plage As Range, cell As Range, NoCol as string
    NoCol = " 1, 2, 3, 4, 11, 14, 18, 20, 21, 23, "
    Set Plage = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
    Range("A2:A"& cells(columns(1).cells.count, 1).End(xlUp).Row).entirerow.hidden = True
    For Each cell In Plage
        If InStr(NoCol, " " & cell.Column & ", ") = 0 Then
            cell.EntireRow.Hidden = False
        End If
    Next
End Sub

En rouge, le No de ligne modifié
A+

Edit
En fin de compte, je ne sais pas si intersect serait plus sioux

[jeo13]

Yes normalement ca devrait aller'il faut que je reteste, mais la on ne teste pas sur le dernier champ ou la valeur ne doit pas valoir 'No relation' ou 'Prospect c'est bien ca?

[ouskel'n'or]

J'ai oublié de traiter la colonne "TypeOfCommercial Relation Desc (L1)"
Tu la traites séparément en fin de code
Code corrigé

Code :

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

1
2
3
4
5
Dim i as long
For i = 2 To Range("A1").Cells(Columns(1).Cells.Count, 1).End(xlUp).Row
     If cells(i, 22) = "Prospect" or cells(i, 22) = "No relation" then _
          Rows(i).entirerow.hidden = false
Next

Là, je crois que c'est bon
A+

[jeo13]

Juste une question avant de finir et de mettre en résolu

Pour TypeOfCommercial, dans le code

Code :

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

1
2
3
4
5
Dim i as long
For i = 2 to Range("A1:A"& cells(columns(1).cells.count, 1).End(xlUp).Row)
     If cells(i, 22) = "Prospect" or cells(i, 22) = "No relation" then _
          Rows(i).entirerow.hidden = false
Next

c'est bien A2:A aussi non ?

Parce qu'au final le code ne s'arrete pas dans mon fichier meme si je remplace par A2:A.

En fait il y a deja un tri d'effectué au niveau de la date, ce qui fait que certaines lignes sont masquées. Y a-t-il un impact ou dois je revoir mon code? et faire le tri sur la date apres ce tri la?

Faudra que je teste, la il est tard et je dois quitter le bureau (21h48 ici déja !)

[ouskel'n'or]

Non, ça n'a pas d'importance ici, tu peux mettre A1 ou A2.
Pour le code qui ne finit pas... faudrait voir ton code en entier
A+

[jeo13]

Bonjour,

Alors avant de transposer ce code a mon autre fichier ou il y a déja du code, je l'ai retesté avec mon fichier Exemple.xls précédent.

Voici le fichier avec le bouton permettant le tri.
Il y a un bug au niveau du tri sur la 22eme colonne ("No relation" ou "Prospect")

Et je ne vois pas pourquoi ca coince... Il y a une erreur de type sur la ligne en gras:

Code :

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

1
2
3
4
  Dim i As Long
For i = 2 To Range("A2:A" & Cells(Columns(1).Cells.Count, 1).End(xlUp).Row)
     If Cells(i, 22) = "Prospect" Or Cells(i, 22) = "No relation" Then _
          Rows(i).EntireRow.Hidden = False

Sinon c'est nickel ca enleve bien la ligne qu'il faut...

Ensuite je verrai avec ce code combiné avec mon autre code pourquoi il n'arrete pas de tourner.

Merci !