IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

[Optimisation] Parcourir une feuille sans boucle/optimisation de boucle [XL-2007]


Sujet :

Macros et VBA Excel

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre éprouvé Avatar de LawNasK
    Homme Profil pro
    Étudiant
    Inscrit en
    Janvier 2015
    Messages
    144
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Côte d'Or (Bourgogne)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Industrie

    Informations forums :
    Inscription : Janvier 2015
    Messages : 144
    Par défaut [Optimisation] Parcourir une feuille sans boucle/optimisation de boucle
    Bonjour,

    Je travail actuellement sur une feuille excel avec 24 colonnes et 1000 lignes qui fait office de base de données.

    Dans cette base, je dois supprimer certaines entrées via une macro, pour le fonctionnement de mes macro suivantes.

    (Pour des raisons de confidentialités, j'ai remplacé divers éléments par des noms de félin)


    Voici un exemple de code de suppression d'entrées. Il y en a trois qui fonctionnent toutes sur le même principe.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    'Efface les lignes dont le métier = Tigre
    Sub SupressionDesTigres()
        Dim lynx As Integer
     
        With ThisWorkbook.Worksheets("Chat")
            For lynx = 2 To LNK.nombreDeLignes("G") 'LNK.nombreDeLignes(colonne As String) donne le nombre de lignes de la colonne choisie
                If .Range("G" & lynx) = "Tigre" Then
                    Rows(i & ":" & lynx).ClearContents
                End If
            Next
        End With
     
        Call LNK.SuppressionLignesVides 'Supprime toutes les lignes entièrement vides de la feuille active
    End Sub
    J'ai aussi une procédure pour supprimer les colonnes :

    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
    'Efface les colonnes qui ne sont pas utile pour le programme
    Sub TriParColonnes()
        Dim colonne As Integer
        Dim nbLignes As Integer
        Dim nomColonne As String
     
        Set colonnesASupprimer = CreateObject("System.Collections.ArrayList")
     
        'Les colonnes a choisir peuvent changer selon les années. Elles sont donc toutes notés ici, mais commentées si inutiles pour l'année en cours
     
        'colonnesASupprimer.Add("Lion")
        'colonnesASupprimer.Add("Guépard")
        colonnesASupprimer.Add("Jaguar")
        colonnesASupprimer.Add("Puma")
        'colonnesASupprimer.Add("Couguar")
        'colonnesASupprimer.Add("Panthère")
        '.... .... ....
        colonnesASupprimer.Add ("Léopard")
        colonnesASupprimer.Add ("Jaguarondi")
        colonnesASupprimer.Add ("Margay")
        colonnesASupprimer.Add ("Ocelot")
        'colonnesASupprimer.Add("Caracal")
        'colonnesASupprimer.Add("Serval")
     
        nbLignes = LNK.nombreDeLignes("A") 'LNK.nombreDeLignes(colonne As String) donne le nombre de lignes de la colonne choisie
     
        With ThisWorkbook.Worksheets("Chat")
            For colonne = 24 To 1 Step -1
                If colonnesASupprimer.Contains(Cells(1, colonne).Value) Then
                    Range(.Cells(1, colonne), .Cells(nbLignes, colonne)).Delete Shift:=xlToLeft
                End If
            Next
        End With
     
        Columns("A:X").EntireColumn.autofit
    End Sub
    Tout fonctionne comme il faut mais j'ai des gros problèmes de lenteur. Voilà le temps d’exécution (en secondes) par modules de mon programme :
    RoyaumeDesCanidés : 1,37109375
    RoyaumeDesFelins : 221,30859375
    RoyaumeDesOiseaux : 0,01171875
    RoyaumeDesPoissons : 0,72265625
    RoyaumeDesHommes : 0,40234375
    RoyaumeDesReptiles : 0,6875

    Total : 224,53515625
    Ce temps d’exécution n'étant pas acceptable pour mon entreprise, je cherche un moyen d'optimiser mon code depuis quelque temps, mais je n'ai pas trouvé pour l'instant. Je me tourne donc vers vous pour savoir si vous connaissez un moyen d'améliorer la vitesse d’exécution de la macro, et si oui, lequel.

    Merci d'avance pour vos réponses !

  2. #2
    Expert confirmé Avatar de casefayere
    Homme Profil pro
    RETRAITE
    Inscrit en
    Décembre 2006
    Messages
    5 138
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 71
    Localisation : France, Ardennes (Champagne Ardenne)

    Informations professionnelles :
    Activité : RETRAITE
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : Décembre 2006
    Messages : 5 138
    Par défaut
    Bonjour,
    un début pour le 1er problème (juste une idée à adapter), en passant par une variable tableau plus rapide)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    Sub SupressionDesTigres()
    Dim lynx As Long
    Dim Tb, Dc As Range
    With ThisWorkbook.Worksheets("Chat")
      Set Dc = .Range("G" & .Rows.Count).End(xlUp)
      Tb = .Range("G2", Dc)
      For lynx = 1 To UBound(Tb, 1)
        If Tb(lynx, 1) = "Tigre" Then Tb(lynx, 1) = ""
      Next lynx
      .Range("G2").Resize(UBound(Tb, 1)) = Tb
    End With
    .....
    quant à cette ligne
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Call LNK.SuppressionLignesVides 'Supprime toutes les lignes entièrement vides de la feuille active
    tu n'as pas éditer la procédure donc...à voir
    Cordialement,
    Dom
    _____________________________________________
    Vous êtes nouveau ? pour baliser votre code, cliquer sur cet exemple : Anomaly
    pensez à cliquer sur :resolu: si votre problème l'est
    Par contre, il est désagréable de voir une discussion résolue sans message final du demandeur (satisfaction, désarroi, remerciement, conclusion...)

  3. #3
    Membre Expert
    Homme Profil pro
    Ingénieur développement matériel électronique
    Inscrit en
    Septembre 2013
    Messages
    783
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Ingénieur développement matériel électronique
    Secteur : High Tech - Électronique et micro-électronique

    Informations forums :
    Inscription : Septembre 2013
    Messages : 783
    Par défaut
    Bonjour,

    Pas étonnant que ça soit lent .... une suppression ligne par ligne est toute désignée pour les pauses cafés surtout sil le screenupdate n'est pas mis à 'False'

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    If .Range("G" & lynx) = "Tigre" Then
    => Méthode que je préconise:
    1. filtrer sur les valeurs que vous voulez supprimer
    2. supprimer en une passe toutes les lignes concernées en passant par l'objet
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    specialcells(xlCellTypeVisible)(
    Vous pouvez par exemple piloter le filtrage par (exemple)

    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
    Sub AutoFilter_by_ArrayParam()
    Dim Wks As Worksheet	
    Dim InpRng As Range
    On Error GoTo Err_Filter	
    Dim arCriteria(0 To 1) As String	
     
    Set Wks = ActiveSheet	
    arCriteria(0) = "Loeb"	
    arCriteria(1) = "Vettel"	
     
    Set InpRng = Wks.Range("A1".
    InpRng.AutoFilter Field:=2, Criteria1:=arCriteria, Operator:=xlFilterValues
     
    Debug.Print InpRng.specialcells(xlCellTypeVisible).Address
     
    Err_Filter:	If Err <> 0 Then (...)

  4. #4
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Août 2010
    Messages
    3 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Août 2010
    Messages : 3 453
    Par défaut
    Bonjour,

    Voir peut être avec un filtre élaboré (copie du résultat sous la plage et ensuite suppression de cette dernière) :
    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
     
    Sub Filtrer()
     
        Dim Plage As Range
        Dim ZoneCritere As Range
     
        With ActiveSheet
     
            'les deux premières lignes pour la zone de critéres
            'sur la 1ère ligne, les entêtes de colonnes et sur la seconde le
            'ou les critères comme par exemple en G2 "Tigre"
            Set ZoneCritere = .Range(.Cells(1, 1), .Cells(2, 10))
     
            'la plage à filtrer qui commence en ligne 4 afin de laisser une
            'ligne vide entre la plage et la zone de critères
            Set Plage = .Range(.Cells(4, 1), .Cells(.Rows.Count, 10).End(xlUp))
     
        End With
     
        'la plage est filtrée et le résultat est collé sous la plage...
        Plage.AdvancedFilter xlFilterCopy, ZoneCritere, Plage.Rows(Plage.Rows.Count).Offset(1, 0), False
     
        'puis cette dernière est supprimée
        Plage.Delete
     
    End Sub
    Hervé.

  5. #5
    Membre éprouvé Avatar de LawNasK
    Homme Profil pro
    Étudiant
    Inscrit en
    Janvier 2015
    Messages
    144
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Côte d'Or (Bourgogne)

    Informations professionnelles :
    Activité : Étudiant
    Secteur : Industrie

    Informations forums :
    Inscription : Janvier 2015
    Messages : 144
    Par défaut
    Merci à tous pour vos réponses !

    J'ai utilisé la méthode des filtres et le résultat est plutôt concluant :

    RoyaumeDesFelins : 0,5078125
    Pour info, voici mon code final :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
        With ThisWorkbook.Worksheets("chat")
            .Activate
            .Range("A1:X" & LNK.nombreDeLignes("A")).AutoFilter _
                Field:=7, _
                Criteria1:="Tigre"
     
            .Range("A2:X" & LNK.nombreDeLignes("A")).SpecialCells(xlCellTypeVisible).EntireRow.Delete
     
            Selection.AutoFilter
        End With
    je n'ai pas eu besoin de changer ma procédure pour supprimer les colonnes, car une fois les lignes courtes, l’exécution est rapide.

    Encore merci !! ♥

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 4
    Dernier message: 04/07/2009, 15h05
  2. [VBA-E] imprimer une feuille sans connaitre le nom
    Par srame dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 19/04/2007, 09h02
  3. [VBA-Excel]Aller sur une feuille sans l'activer.
    Par bonilla dans le forum Macros et VBA Excel
    Réponses: 5
    Dernier message: 04/10/2006, 12h50
  4. [VBA-E]Récuperer le nom d'une feuille sans le N°
    Par Elstak dans le forum Macros et VBA Excel
    Réponses: 5
    Dernier message: 04/04/2006, 11h33
  5. [EXCEL] copier une feuille sans changer les liaisons
    Par DidRocks dans le forum Macros et VBA Excel
    Réponses: 1
    Dernier message: 09/09/2005, 13h29

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo