XL 2019 VBA : supprimer colonnes / lignes si conditions + MFC

[ruliann]

hello

Je récupère un tableau brut d'un fournisseur, avec de nombreuses lignes (environ 500) et colonnes (parfois jusqu'à 200) et j'aimerais mettre en page ce tableau.

Sur le tableau ci-joint, je souhaiterais :

• supprimer les colonnes que j'ai surlignées en jaune (la condition : que le contenu de la cellule de la 2ème ligne, depuis la colonne I jusqu'à la dernière colonne renseignée, soit égal à 0)
• supprimer les lignes que j'ai surlignées en rouge (la condition : que toutes les cellules de la ligne, depuis la colonne I jusqu'à la dernière colonne renseignée, soient égales à 0)

Et une fois ce tri effectué, j'aimerais appliquer un code couleur, comme l'exemple réalisé manuellement sur la ligne 5. J'avais pensé utilisé une MFC, mais vu que les valeurs des "prix" situées sur les colonnes D à H varient pour chaque ligne, cela veut veut dire que je dois paramétrer des MFC pour chaque ligne??? (ce qui prendrait beaucoup de temps)

• pour le code couleur, n'y a-t-il pas une autre manière de faire qu'en paramétrant des règles de MFC pour chaque ligne?

merci pour votre aide, pour le moment je ne suis pas allé bien loin. Ce code destiné à supprimer les colonnes fonctionne, mais je dois relancer la macro à chaque fois, pour chaque colonne...

Sub Efface_colonne()

     For Each C In Range("I2:AM2")
        If C = "0" Then C.EntireColumn.Delete
    Next

End Sub

 

[Lolote83]

Salut,
Remplace ce code pour la suppression des colonnes

Sub SupprimeColonne()
    Application.ScreenUpdating = False
    xDerCol = Range("XFD2").End(xlToLeft).Column
    For F = xDerCol To 8 Step -1
        If Cells(2, F) = 0 Then
            Cells(2, F).EntireColumn.Delete
        End If
    Next F
    Application.ScreenUpdating = True
End Sub

et pour les lignes

Sub SupprimeLigne()
    Application.ScreenUpdating = False
    xDerLig = Range("A65000").End(xlUp).Row
    For F = xDerLig To 4 Step -1
        xTotal = Application.Sum(Range("I" & F & ":S" & F))
        If xTotal = 0 Then
            Rows(F & ":" & F).EntireRow.Delete
        End If
    Next F
    Application.ScreenUpdating = True
End Sub

Pour le reste je regarde plus tard
@+ Lolote83

 

[Robert]

Bonjour Ruliann, bonjour le forum,

Pas évident à comprendre :

• supprimer les colonnes que j'ai surlignées en jaune (la condition : que le contenu de la cellule de la 2ème ligne, depuis la colonne I jusqu'à la dernière colonne renseignée, soit égal à 0)

Dans ton exemple tu as coloré en jaune les 2ème et 3ème lignes de T jusqu'à la dernière colonne et pas de I jusqu'à la dernière colonne ?!...

Quant aux lignes rouges, les lignes 10, 12 et 15 respectent la condition mais tu ne les as pas surlignées ?!...

[Édition]
Bonjour Lolote, je navet pas rafraîchi et nos posts se sont croisés...

 

[ruliann]

Bonjour à vous deux

Les données qui sont en cellules "A1:H51" ne changent pas. En colonne D à H ce sont des seuils de prix à ne pas dépasser.

Toutes les jours je reçois des données fournisseurs concernant les prix de certains articles : les prix des ces articles correspondent aux chiffres qui sont en colonne I, J , K.... Hier par exemple, j'ai reçu les prix de 11 articles (d'où les 11 colonnes de J à S). Mais des fois je peux recevoir la mise à jour de 200 prix... donc 200 colonnes, de la colonne J à J+200....

J'aimerais que la mise en page ne tienne pas compte (supprime) les colonnes pour lesquelles l'en-tête (la ligne 2) est = à 0 (ou bien des fois à la place de 0 je peux aussi avoir #REF).

@Robert : pour les lignes 10,12,15 tu as raison, elles auraient du être en rouge mais je les ai oubliées.


merci Lolote83, je pense avoir réussi à intégrer tes codes, pour le moment cela fonctionne

C'est surtout la MFC qui m'inquiète car je ne sais pas s'il y a un moyen de passer autrement que définir des règles ligne par ligne?

 

[Lolote83]

Re bonjour,
Peux tu nous en dire plus concernant les MFC que tu souhaites mettre en place car actuellement, au vue du fichier je ne comprends pas.
De plus, dans ton fichier, certaines cellules sont sous la forme >20, <10 donc texte et non numérique!!!!
Merci de nous dire
@+ Lolote83

 

[ruliann]

@Lolote83 : pas évident de comprendre le code couleur car j'avais fait une erreur :/

Voici un exemple :

• la cellule I5 a un fond vert car elle est > à 1 (D5) mais < à 6 (E5)

Cela veut dire que pour I5, il faut que je compare la valeur de I5 avec la valeur de D5, E5, F5, G5, H5 afin que je détermine à quel "seuil" correspond I5, puis que je colorie la cellule I5 avec le même code couleur que le seuil concerné...et les #N/A m'embête un poil

Pour ce qui est du texte numérique genre >20, c'est un souci que je répète au fournisseur, en vain pour le moment...

j'espère que c'est + clair

 

[Lolote83]

Re bonjour,
Voici ton fichier en retour.
Une macro pour les couleurs a été faite.
Voici donc le résultat. Est-ce que c'est cela qui est attendu ?
@+ Lolote83

 

[ruliann]

hello
tu touches au but. Il y a encore quelques trucs à régler, par exemple :

• en J5, la cellule devrait être colorée en bleu, (puisque J5 est < à D5)
• pour les cellules I6 J6 et K6, elles devraient être colorées en jaune puisqu'elles sont < à F6 (on exclut les codes couleurs D6 et E6 puisque ce sont des #N/A)
• de I28 à R28, toutes les cellules devraient être bleu puisqu'elles sont < à D28

En parallèle, j'avais recherché une solution via des MFC avec des formules mais c'est peine perdue vu le nombre de lignes à traiter

merci pour ton aide qui est la bienvenue

 

[Lolote83]

Re bonjour,
Voici donc le fichier mis à jour en fonction des remarques.
Un simple >= a remplacer par un <=
@+ Lolote83

 

[ruliann]

je me suis mal expliqué

• en I4 le résultat est 2. I4 > D4 donc la cellule devrait être verte
• en J4 le résultat est 10. J4 > D4 et J4 > E4 et J4 < F4 donc la cellule devrait être jaune
• en O4 le résultat est 12. O4 > D4 et O4 > E4 et O4 < F4 donc la cellule devrait être orange clair
• en Q4 le résultat est 4. Q4 > D4 et Q4 < E4 donc la cellule devrait être verte

j'ai l'impression qu'il y a un décalage d'une colonne par rapport aux seuils

 

[ruliann]

hello

J'ai simplifié ma demande sur le fichier ci-joint (et j'espère la solution).

Je cherche à copier/coller des MFC de cellule en cellule, via une macro, au lieu de faire "copier" / "coller format" à la main.

Est-ce possible d'après vous? merci pour votre aide

 

[Lolote83]

Salut RULIANN,
Je ne sais pas pourquoi mais je commençais en ligne 5 au lieu de ligne 4 d'ou ta remarque au post#10.
Du coup, voici le fichier modifié sans avoir pour l'instant regardé ton nouveau fichier.
Dis moi et je regarde ton fichier en parallèle.
@+ Lolote83

 

[ruliann]

c'est ça t'as tout compris bravo, ton fichier fonctionne bien!

à présent faut que je transpose ton code dans le fichier original.

Pour le 2ème fichier, je n'arrivais à recopier les MFC autrement au'en copiant/collant le format de chacune des cellules, une par une. Même em modifiant les attributs $ des cellules , le collage ne fonctionnait pas.

Peut-être que je ne n'aurai pas besoin de cette 2ème solution si je parviens à transposer ton code dans mon fichier, je te tiens au courant. merci encore!

 

[Lolote83]

Re bonjour.
Ok . J'attends donc une réponse concernant l'intégration du code dans ton fichier d'origine.
Bon courage.
@+ Lolote83

 

[ruliann]

cool ca fonctionne très bien encore merci

sans abuser de ton temps, si à l'occasion tu peux m'indiquer comme faire la macro du 2ème fichier, ça me fera un exercice pour débuter VBA en essayant de comprendre ton code (c'est juste de la curiosité de ma part)
bon ap