XL 2010 Traitement macro très très long, est-il possible de raccourcir le temps de traitement ?

Philippe LAMACHE · 7 Octobre 2019

Bonjour à tous,

Comme je le demande dans l'objet de ce post, y aurai-t'il une solution pour accélérer le traitement de cette macro ?
(environ 30 mn pour 50 000 lignes actuellement)

VB:

Sub Ajout_Col()
MaFeuil = Feuil3.[A1048576].End(xlUp).Value
Worksheets(MaFeuil).Select
dercol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
Range([A14], [A14].SpecialCells(xlLastCell)).Select
Selection.Resize(, 1).Select
Selection.Offset(0, dercol).Select
Selection.Offset(-1, 0).Value = "RGF"
Selection.Offset(-1, 1).Value = "RGF2"
For Each c In Selection
    txt = Len(c.Offset(0, -28)) + Len(c.Offset(0, -27))
    If txt > 1 Then
        MaValue = c.Offset(0, -35).Value & " " & c.Offset(0, -34).Value & "-" & c.Offset(0, -27).Value
        MaValue1 = c.Offset(0, -35).Value & " " & _
                    c.Offset(0, -34).Value & " " & _
                    c.Offset(0, -31).Value & " " & _
                    c.Offset(0, -30).Value & " " & _
                    c.Offset(0, -28).Value & " " & _
                    c.Offset(0, -27).Value
    Else
        MaValue = c.Offset(0, -35).Value & " " & c.Offset(0, -34).Value & "-" & c.Offset(0, -30).Value
        MaValue1 = c.Offset(0, -35).Value & " " & _
                    c.Offset(0, -34).Value & " " & _
                    c.Offset(0, -31).Value & " " & _
                    c.Offset(0, -30).Value
    End If
    If c.Offset(0, -35) <> "" Then
        c.Value = MaValue
        c.Offset(0, 1).Value = MaValue1
    ElseIf c.Offset(0, -35).Value = " " Then
        Exit Sub
    End If
Next c
End Sub

Par avance, merci.

mapomme · 7 Octobre 2019

Bonjour @Philippe LAMACHE ,

Le minimum syndical est de fournir un petit fichier test anonymisé (et accessoirement de décrire ce que la macro est censée faire)

mapomme · 7 Octobre 2019

Re,

La dernière condition de la boucle semble bizarre.

Si c.Offset(0, -35) <> "" alors on fait des choses (donc y compris si c.Offset(0, -35) = " ") et on continue la boucle
sinon si c.Offset(0, -35) = " ", on sort de la macro.

Pour moi, il y a incohérence => la condition sinon si ne sera jamais exécutée. Si c.Offset(0, -35) = " " alors on a forcément c.Offset(0, -35) <> "" donc on aura exécuté d'abord la première partie du si...

Philippe LAMACHE · 7 Octobre 2019

Bonjour "mapomme",

Voici le fichier demandé.

Je voudrais concaténer certaines cellules (colonnes B, C, G ou I) dans une colonne et dans un autre (colonnes B, C, G, G, I, J)
Je le fais par une boucle for mais c'est très long de traiter ainsi (mini 25 000 lignes, max 50 000) donc je posais la question de savoir si cela était possible d'accélérer le traitement autrement que par une boucle.

Philippe LAMACHE · 7 Octobre 2019

mapomme à dit:
Re,

La dernière condition de la boucle semble bizarre.

Si c.Offset(0, -35) <> "" alors on fait des choses (donc y compris si c.Offset(0, -35) = " ") et on continue la boucle
sinon si c.Offset(0, -35) = " ", on sort de la macro.

Pour moi, il y a incohérence => la condition sinon si ne sera jamais exécutée.

Oui je sais, mais bizarrement, si je ne mets pas cette ligne ma boucle ne veut pas s'arrêter.

patricktoulon · 7 Octobre 2019

Bonjour
perso j'ai deja du mal avec ceci

VB:

Sub Ajout_Col()
MaFeuil = Feuil3.[A1048576].End(xlUp).Value
Worksheets(MaFeuil).Select
dercol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
Range([A14], [A14].SpecialCells(xlLastCell)).Select
Selection.Resize(, 1).Select
Selection.Offset(0, dercol).Select

le but final est il de prendre la cellule (dercol,derligne de "A") dans feuil1???
et pourquoi tout ces selects ???( qui sont certainement responsable de la lenteur)
et Range([A14], [A14].SpecialCells(xlLastCell)).Select c'est quoi cette formulation,"A:A" c'est pas bien ?

Philippe LAMACHE · 7 Octobre 2019

patricktoulon à dit:
Bonjour
perso j'ai deja du mal avec ceci

VB:

Sub Ajout_Col() MaFeuil = Feuil3.[A1048576].End(xlUp).Value Worksheets(MaFeuil).Select dercol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column Range([A14], [A14].SpecialCells(xlLastCell)).Select Selection.Resize(, 1).Select Selection.Offset(0, dercol).Select

le but final est il de prendre la cellule (dercol,derligne de "A") dans feuil1???
et pourquoi tout ces selects ???( qui sont certainement responsable de la lenteur)
et Range([A14], [A14].SpecialCells(xlLastCell)).Select c'est quoi cette formulation,"A:A" c'est pas bien ?

Bonjour "patricktoulon",
Heu ... comment dire ... Comme je suis une bille en VBA, c'est la seule façon avec laquelle j'ai réussi à sélectionner la dernière colonne non vide de ma feuille (regarde plus haut dans le post j'ai mis un extrait avec peu de lignes et 1 seule feuille).
Et en fait, ma macro telle quelle fonctionne bien mais la boucle FOR est très longue à exécuter quand j'ai des dizaines de milliers de lignes !)

Dranreb · 7 Octobre 2019

Bonjour.
Vous auriez probablement intérêt à passer par deux formules

Philippe LAMACHE · 7 Octobre 2019

Dranreb à dit:
Bonjour.
Vous auriez probablement intérêt à passer par deux formules

Bonjour "Dranreb",
Au début, je l'avais fait mais j'ai une dizaine de feuilles (entre 20 000 & 50 000 lignes chacune) à traiter et avec des formules le fichier plante au bout d'un moment et il devient extrêmement volumineux (déjà qu'il fait environ 50 Mo sans le traitement, juste à insérer mes feuilles)

Dranreb · 7 Octobre 2019

Si vous ne pouvez pas garder les formules à la fin, faites un RgnRGF.Value = RngRGF.Value avant la End Sub
étant déclaré Dim RngRGF As Range et ayant fait l'objets d'un Set RngRGF = quelque chose et où vous aurez fait vos deux RngRGF.Columns(1).FormulaR1C1 = "=RC2&"" ""&…etc et RngRGF.Columns(2).FormulaR1C1 = etc.
Ce qu'il faut éviter à tout prix ce sont les boucles portant sur des cellules individuelles. Pas besoin d'éviter les boucles simples portant sur des éléments de tableaux, ni quelques instructions d'affectations dans les deux sens de tableaux à des plages entières d'un seul coup.

Philippe LAMACHE · 7 Octobre 2019

Dranreb à dit:
Si vous ne pouvez pas garder les formules à la fin, faites un RgnRGF.Value = RngRGF.Value avant la End Sub
étant déclaré Dim RngRGF As Range et ayant fait l'objets d'un Set RngRGF = quelque chose et où vous aurez fait vos deux RngRGF.Columns(1).FormulaR1C1 = "=RC2&"" ""&…etc et RngRGF.Columns(2).FormulaR1C1 = etc.

Merci, je vais essayer.

Philippe LAMACHE · 7 Octobre 2019

Philippe LAMACHE à dit:
Merci, je vais essayer.

Re "Dranreb",
Après quelques péripéties (parce que je ne comprenais pas bien ce que tu as écris), j'en suis arrivé à ce code:

VB:

Sub Ajout_Col()
MaFeuil = Feuil3.[A1048576].End(xlUp).Value
Worksheets(MaFeuil).Select
dercol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
Range([A14], [A1048576].End(xlUp)).Select
Selection.Resize(, 1).Select
Selection.Offset(0, dercol).Select
    Selection.FormulaR1C1 = _
        "=RC[-35]&"" ""&RC[-34]&IF(LEN(RC[-28])+LEN(RC[-27])>1,""-""&RC[-27],""-""&RC[-30])"
Selection.Formula = Selection.Value
Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = _
        "=RC[-36]&"" ""&RC[-35]&IF(LEN(RC[-29])+LEN(RC[-28])>1,"" ""&RC[-32]&"" ""&RC[-31]&"" ""&RC[-29]&"" ""&RC[-28],"" ""&RC[-32]&"" ""&RC[-31])"
Selection.Formula = Selection.Value
End Sub

Cela me donne le résultat voulu et cela se fait en une fraction de seconde.
Merci "Dranreb" pour ton aide. Bonne journée.

Philippe LAMACHE · 7 Octobre 2019

Philippe LAMACHE à dit:
Bonjour "patricktoulon",
Heu ... comment dire ... Comme je suis une bille en VBA, c'est la seule façon avec laquelle j'ai réussi à sélectionner la dernière colonne non vide de ma feuille (regarde plus haut dans le post j'ai mis un extrait avec peu de lignes et 1 seule feuille).
Et en fait, ma macro telle quelle fonctionne bien mais la boucle FOR est très longue à exécuter quand j'ai des dizaines de milliers de lignes !)

Re "patricktoulon",
Comme tu pourras le voir dans mon post ci-dessus, j'ai modifié ma ligne Range([A14], [A......... et c'est vrai que c'est mieux.
Merci pour ton aide. Bonne journée.

Dranreb · 7 Octobre 2019

Reste plus qu'à contracter les paires TelObjet.Select suivi de Selection.MéthodeOuPropriété en une seule instruction TelObjet.MéthodeOuPropriété.
Et si c'est plus compliqué on utilise des variables de types Workbook, Worksheet ou Range, mais jamais Select et Selection. Ça c'est l'enregistreur de macro qui fait comme ça, juste pour rester fidèle aux changements d'états entrainés par les actions effectuées et non seulement à leur résultat

Dranreb · 7 Octobre 2019

Ça peut aboutir à du code bien plus court comme ça par exemple :

VB:

Sub Ajout_Col2()
   Dim Wsh As Worksheet, Rng As Range
   Set Wsh = Worksheets(Feuil3.[A1048576].End(xlUp).Value)
   Set Rng = Wsh.[XFD13].End(xlToLeft).Offset(1, 1).Resize(Wsh.[A1048576].End(xlUp).Row - 13, 2)
   Rng.Columns(1).FormulaR1C1 = "=RC2&"" ""&RC3&IF(LEN(RC9)+LEN(RC10)>1,""-""&RC10,""-""&RC7)"
   Rng.Columns(2).FormulaR1C1 = "=RC2&"" ""&RC3&"" ""&RC6&"" ""&RC7&IF(LEN(RC9)+LEN(RC10)>1,"" ""&RC9&"" ""&RC10,"""")"
   Rng.Value = Rng.Value
   End Sub

XL 2010 Traitement macro très très long, est-il possible de raccourcir le temps de traitement ?

Philippe LAMACHE

XLDnaute Junior

mapomme

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Philippe LAMACHE

XLDnaute Junior

Pièces jointes

Philippe LAMACHE

XLDnaute Junior

patricktoulon

XLDnaute Barbatruc

Philippe LAMACHE

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

Philippe LAMACHE

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

Philippe LAMACHE

XLDnaute Junior

Philippe LAMACHE

XLDnaute Junior

Philippe LAMACHE

XLDnaute Junior

Dranreb

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Discussions similaires