Optimisation macro trop lente - Afficher / Masquer ligne

Le 16/09/2022 à 18:26

Bonjour,

En fonction des valeurs de la colonne "J" je dois masquer des lignes avec une case à cocher.

- case à cocher pleine = ligne visible

- case à cocher vide = ligne masquée

Il faut pas loin d'une minute à Excel pour exécuter ce code. voir ci-dessous.
Et ça peut importe "J19:J100" ou "J19:J1000".

Ma macro demande à excel d’exécuter le code ligne par ligne, ce qui je pense, n'est pas optimal.

Auriez-vous une idée pour un code plus esthétique et plus optimisé ?

Remarque :

c'est un fichier d'entreprise utilisé par bcp de collaborateurs.

Je ne peux utiliser le tri tableau car ce dernier s'applique à l'ensemble des en-têtes colonnes.

Or je ne veux pas que les collabo y touche.

Si vous savez comment trier une seule colonne d'un tableau je suis preneur.

Merci d'avoir pris le temps de me lire.

Au plaisir de lire ta réponse.

Alex.

Private Sub CheckBox28_Click()
Application.ScreenUpdating = False

Dim cellule As Range

    If CheckBox28 Then

            For Each cellule In Range("J19:J1000")
                If cellule.Value = "SOM" Then
                    cellule.EntireRow.Hidden = False
                End If
            Next cellule

    Else
            For Each cellule In Range("J19:J1000")
                If cellule.Value = "SOM" Then
                    cellule.EntireRow.Hidden = True
                End If
            Next cellule

    End If
End Sub
Le 16/09/2022 à 19:04

Bonjour Dual, bonjour le forum,

Si tu avais fourni un fichier exemple j'aurais pu tester....

Essaie comme ça :

Private Sub CheckBox28_Click()
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim DL As Integer 'déclare la variable DL (Dernière Ligne)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim I As Integer 'déclare la variable I (Incrément)
Dim K As Integer 'déclare la variable K (incrément)
Dim TL() As Variant 'déclare la variable TL (Tableau des Lignes)
Dim PL As Range 'déclare la variable PL (PLage)

Set O = Worksheets("Feuil1") 'définit l'onglet O (à adapter à ton cas)
DL = O.Cells(Application.Rows.Count, "J").End(xlUp).Row 'définit la dernière ligne éditée DL de la colonne J de l'onglet O
TV = O.Range("J19:J" & DL) 'définit la tableau des valeurs TV
For I = 1 To UBound(TV, 1) 'boucle sur toutes les lignes I de TV
    If TV(I, 1) = "SOM" Then 'condition : si la donnée ligne I, colonne 1 de TV est égale à "SOM"
        K = K + 1 'Incrémente K
        ReDim Preserve TL(1 To K) 'redimensionne le tableau de ligne TL (K lignes)
        TL(K) = I + 18 'renvoie dans TL la ligne I+18 (puisque le début est J19)
    End If 'fin de la condition
Next I 'prochaine ligne de la boucle
For I = 1 To UBound(TL, 1) 'boucle sur toutes les lignes I de TL
    'si la plage PL est vide définit la plage PL comme la ligne I de TL, sinon définit, la plage PL comme l'union de PL et de la ligne I de TL
    If PL Is Nothing Then Set PL = O.Rows(TL(I)) Else Set PL = Application.Union(PL, O.Rows(TL(I)))
Next I 'proicbhaine ligne de TL
PL.Hidden = CheckBox28.Value 'affiche/masque la plage PL en fonction de la valeur de la CheckBox28
End Sub
Le 16/09/2022 à 19:08
Private Sub CheckBox28_Click()
     Application.ScreenUpdating = False

     Dim cellule As Range

     Set c = Range("J19:J1000")     'votre plage
     c.EntireRow.Hidden = False     'montrer toutes les lignes
     Set un = Range("J18")     'une cellule dehors c

     If Not CheckBox28 Then
          For Each cellule In c.Cells
               If cellule.Value = "SOM" Then Set un = Union(un, cellule)     'ajouter à la plage
          Next cellule
          Set un = Intersect(un, c)     'exclure la cellule qui était dehors
          If Not un Is Nothing Then un.EntireRow.Hidden = True     'cacher si nécessaire
     End If
End Sub
Le 17/09/2022 à 21:57

Bonsoir TauThème, Bonsoir BsAlv,

Je vous remercie pour votre réactivité et votre altruisme.

Je joint un excel reprenant vos deux solutions : Feuille "1" = BsAlv / Feuille "2" = TauThème

TauThème : tu m'as tout l'air d'être un expert ++ du codage ! Je n'ai pas réussi à le faire fonctionner.

Erreur d'éxécution '1004'

impossible de définir la propriété Hidden de la classe range

Et je n'ai pas les capacités nécessaires pour l'adapter correctement, j'en suis désolé.

BsAlv : J'ai pu lire, adapter ton code ! c'est exactement ce dont j'avais besoin pour la checkbox 28. Ça fonctionne.

Néanmoins, j'ai voulu adapter ce code à checkbox 17: (voir le fichier joint)

3xlspratique-test1.zip (620.01 Ko)

- Elle masque les ligne contenant "A" dans la colonne "I"

PROBLEME 1 :

- quand j'affiche les lignes A masqué, les référence en sommeil "SOM" colonne"J" réapparaissent aussi. Ces références en sommeil doivent resté cachées.

PROBLEME 2 :

- Je masque les réf.de classe A. OK (checkbox 17 "Beauté")

- Je masque les réf.de classe B. (checkbox 18 "Bien-être") et les lignes contenant "A" réaparaissent !!!

Peux-tu venir me filer un coup de pouce stp.

Je te remercie par avance.

Au plaisir.

Alex.

Le 17/09/2022 à 22:44

bonsoir,

c'est maintenant avec une macro commun avec 2 variables et puis une macro pour chaue checkbox qui lance cette macro. Pour la vitesse, j'ai utilisé c.SpecialCells(xlConstants), en supposant qu'aucune des cellules de I19:I1000 contient une formule, IMPORTANT !!! (Celles qui contiennent une formule ne seront pas traitées)

2xlspratique-test1.zip (631.56 Ko)
Sub CheckBox(MaClasse, bCacher)
     '**********************************************************************
     'macro générale pour toutes les Checkboxes
     'on ajoute les 2 variables : MaClasse = string de la classe et bCacher = valeur du Checkbox
     '**********************************************************************

     Dim cellule As Range, UN

     Set c = Range("I19:I1000")
     Set UN = Range("J18")     'une cellule dehors c

     For Each cellule In c.SpecialCells(xlConstants)     'votre plage CONDITION : ces cellules ne sont pas le résultat d'une formuleCells
          If cellule.Value = MaClasse Then Set UN = Union(UN, cellule)     'toutes les cellules contenant "A"
     Next cellule
     Set UN = Intersect(UN, c)     'exclure la cellule qui était dehors

     If Not UN Is Nothing Then UN.EntireRow.Hidden = Not bCacher    'cacher si nécessaire
End Sub

Private Sub CheckBox17_Click()
     CheckBox "A", CheckBox17.Value
End Sub

Private Sub CheckBox18_Click()
     CheckBox "B", CheckBox18.Value
End Sub

Private Sub CheckBox19_Click()
     CheckBox "C", CheckBox19.Value
End Sub

etc.
Le 18/09/2022 à 21:00

Bonsoir le fil, bonsoir le forum,

Oui désolé, iIl maquait EntireRow dans cette ligne :

PL.Hidden = CheckBox28.Value 'affiche/masque la plage PL en fonction de la valeur de la CheckBox28

qui devient :

PL.EntireRow.Hidden = CheckBox28.Value 'affiche/masque la plage PL en fonction de la valeur de la CheckBox28

Mais la solution de BsAlv me paraît bien mieux...

Le 18/09/2022 à 21:04

Bonsoir BsAlv,

Super pour ton code : il permet de cacher "A" puis de cacher "B"... sans que "A" ne revienne lorsque l'on cache "B".

Pour répondre au sujet et pour ceux qui passent par là, l’exécution du code va nettement plus vite !

c.SpecialCells(xlConstants)

Je te confirme : il n'y a pas de réf. à des cellule avec formule(s).

pour finir, et ce sujet sera clôturé, quand j'affiche "A", les "A" sommeil (SOM) réapparaissent toujours et encore !!!

aurais tu une solution stp ?

PS : Merci pour les explication jointes au code. Ce code n'est pas de mon niveau mais c'est en regardant les autres que l'on apprend.

Le 19/09/2022 à 05:16

bonjour le fil,

on sait utiliser le même module avec une valeur optionelle, faux si vous ne dites rien.

La première ligne, Option Compare Text, fait pour tout ce module (ici Feuil13) que les comparaisons textuelles ignorent la différence entre majuscules et miniscules. Sans cela, "SOM" et ("som","Som","sOm","soM",...) sont traité différent.

Il faut en tenir compte qu'il y aura une différence avec les A's si on utilise le checkbox17 avant ou après le checkbox28 (et pour le reste aussi), les 2 ne sont pas (encore) combinés.

3xlspratique-test1.zip (633.53 Ko)
Option Compare Text     '>>>majuscules ou miniscules n'ont pas d'importance dans ce module (toujours premier ligne du module

Sub CheckBox(MaClasse, bCacher, Optional bJ As Boolean)
     '******************************************************************************************
     'macro générale pour toutes les Checkboxes
     'on ajoute les 3 variables : MaClasse = string de la classe et bCacher = valeur du Checkbox
     'le 3ième valeur est une option, valeur vrai ou faux, s'il est vrai, il décale la plage I19:I1000 d'une colonne, défaut=faux=0
     '******************************************************************************************

     Dim cellule As Range, UN As Range

     Set c = Range("I19:I1000").Offset(, -bJ)     'bJ est boolean, alors equal à 0 ou -1, donc I9:I1000 pour faux (0) or J9:J1000 pour vrai (-1)

     For Each cellule In c.SpecialCells(xlConstants)     'votre plage CONDITION : ces cellules ne sont pas le résultat d'une formuleCells
          If cellule.Value = MaClasse Then
               If UN Is Nothing Then Set UN = cellule Else Set UN = Union(UN, cellule)   'toutes les cellules contenant "A"
          End If
     Next cellule

     If Not UN Is Nothing Then UN.EntireRow.Hidden = Not bCacher    'cacher si nécessaire
End Sub

Private Sub CheckBox28_Click()
     CheckBox "SOM", CheckBox28.Value, True     '<<< 3ième valeur=VRAI=décale la plage une colonne vers droite
End Sub
Rechercher des sujets similaires à "optimisation macro trop lente afficher masquer ligne"