Excel 2019, tri hyper lent

Bonjour,

Voilà je suis passé de Excel 2007 à 2019 il y a quelques jours, et j'utilise un classeur de plus de 10000 lignes, sur lequel j'ai plusieurs macros de tris différents. Les tris qui s'effectuaient auparavant en 2 ou 3 secondes sous excel 7 mettent maintenant plus de 10 minutes à répondre.

En mode Excel /safe sans échec au contraire tout va bien, les tris s'exécutent très rapidement.

J'ai essayé avec plusieurs versions sauvegardées de mon classeur dont une avant le changement de version d'excel, rien n'y fait, même si l'on n'active pas les macros et que l'on trie manuellement.

J'ai également essayé de ne trier qu'une fenêtre A1:BH15000 pour limiter le champ aux champs du classeur : c'est pareil !

Quelqu'un pourrait-il m'aider, me donner une piste possible ? j'ai vraiment besoin de ce classeur.

Merci beaucoup par avance.

Patrick

Bonjour et bienvenue,

Joins un extrait de ton fichier avec les procédures VBA.

Le forum pourra alors intervenir.

Cdlt.

Bonjour Patrick et bonjour Jean-Eric,

Tu n'as pas de fonction personnalisée dans ton classeur ?

Essaie avec le fichier joint de faire un tri sur la colonne A pour voir si c'est aussi long.

Chez moi, c'est instantané.

Cdt

Bonjour Eric,

Voilà un petit extrait de mon classeur.

Les macros de tris sont accessibles par les liens dans la cellule Titre en J1.

et ci-dessous l'une des macros de tri :

Sub Tri1_CatGroup()

'

Cells.Select

ActiveWorkbook.Worksheets("Base").Sort.SortFields.Clear

' Tri inversé sur Categ

ActiveWorkbook.Worksheets("Base").Sort.SortFields.Add Key:=Range( _

"C1:C20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

' Tri sur Groupe

ActiveWorkbook.Worksheets("Base").Sort.SortFields.Add Key:=Range( _

"K1:K20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

' Tri sur Tome

ActiveWorkbook.Worksheets("Base").Sort.SortFields.Add Key:=Range( _

"i1:i20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

' Tri sur Titre

ActiveWorkbook.Worksheets("Base").Sort.SortFields.Add Key:=Range( _

"J1:J20000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

With ActiveWorkbook.Worksheets("Base").Sort

.SetRange Range("A1:BH20000")

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

Merci.

ddetp88 a écrit :

Bonjour Patrick et bonjour Jean-Eric,

Tu n'as pas de fonction personnalisée dans ton classeur ?

Essaie avec le fichier joint de faire un tri sur la colonne A pour voir si c'est aussi long.

Chez moi, c'est instantané.

Cdt

PatrickJ.xlsm

Bonjour

désolé je ne sais pas ce qu'est une fonction personnalisée. Il y a des fonctions de calcul, mais relativement simples;

Re,

Peux-tu nous dire quelle procédure est la plus chronophage ?

Cdt

Re,

A tester !?

Pour le principe :

Public Sub SouhaitAchat_PasCher()
' ach_cher Macro  Sélectionner prix rakuten inf à 8€
' puis tri Statut, ma meilleure pos, categ, media
    With Application
        .ScreenUpdating = False
        .Run ("modMain.Main")
    End With
    With ActiveSheet.Sort
        .SortFields.Add Key:=Range("B1"), Order:=xlAscending
        .SortFields.Add Key:=Range("C1"), Order:=xlAscending
        .SortFields.Add Key:=Range("E1"), Order:=xlAscending
        .SetRange rngData
        .Header = xlYes
        .Apply
        .SortFields.Clear
    End With
End Sub

Re,

Après avoir testé les procédures où nous avons accès, les temps de tris sont instantanés. Même si la plage (nb lignes nb colonnes) n'est pas modifiée, il est possible qu'avec 20 000 lignes les temps peuvent sensiblement augmenter.

Quelques remarques :

La première remarque, la plus importante à mon idée, concerne l'installation d'office 2019. Est-ce que ton installation est faite en 64 bits ou en 32 bits?

Si tu es en 64 bits le nombre de lignes est très grand. Il est lié à la mémoire de ton PC si je ne dis pas de bêtise. Là le changement avec 2007 est à considérer.

Ensuite tu as des procédures où tu sélectionnes la plage pour agir dessus. Il faut absolument éviter ce type de programmation.

Exemple quand tu fais

Range("Datemaj").Select

Selection.ClearContents

tu peux faire directement

Range("Datemaj").ClearContents

Tu gagnes un temps considérable. Tu peux aussi désactiver la mise à jour écran avec application.screenupdating=false qui te fera aussi gagner du temps en cas de l'utilisation de .select

Dernière chose, quand tu affectes à une plage à un nom comme "Datemaj", tu affectes la colonne complète.

Il est possible de définir plus finement la plage.

Dans ton gestionnaire de Noms, tu as =base!$A:$A

tu pourrais remplacer cette formule par

=DECALER(base!$A$2;;;NBVAL(base!$A:$A)-1)

Tu me diras, ce n'est pas ce qui va permettre de gagner beaucoup de temps mais c'est plus approprié à ton tableau.

Cdt

Merci Jean-Eric pour tes modifs de macros. J'ai copié mes 11000 lignes dans l'extrait que tu m'as envoyé, puis essayé de lancer le tri T4 (par date), et le problème reste entier malheureusement, Excel ne répond pas. Ce qui est curieux c'est qu'en mode sans échec ça fonctionne parfaitement en moins de 3 secondes.

Merci beaucoup pour les conseils. Comme tu peux facilement le voir, je ne suis pas du tout un pro de VBA, et ces conseils vont m'être très utiles, je vais les appliquer à la lettre

Alors oui mon installation est en 64 bits. Mon ordi est un HP Omen donc assez puissant en principe :

Système d’exploitation Windows 10 Famille 64 bits

Microprocesseur Intel(R) Core(TM) i7-7700HQ CPU @ 2.80GHz

Mémoire système 12 GB

Emplacement mémoire 1 : 8GB Kingston 2667MHz

Emplacement mémoire 2 : 4GB Samsung 2400MHz

C'est aussi pour cela que je suis un peu surpris de ce soucis, et je pense que la mémoire n'est pas enn cause (utilisation forte mais inférieure à 40% par excel).

Sinon je n'utilise pas de procédure avec les noms de plages. Je les avais créées pour facilité dans mes macros, mais je les ai récemment toutes remplacées par leurs valeurs en lignes et colonnes.

Cordialement

Patrick

Re,

D'accord pour les plages mais est-ce que tu fais des tris sur des colonnes entières car le nombre de lignes avec l'installation en 64 bits est de plusieurs millions.

Si tu ne trouves pas de solution rapidement, à ta place je tenterai une réinstallation et cette fois de choisir l'installation en 32 bits.

Cdt

Je ne trie plus par colonne entière. J'avais réduit mes "fenêtres" de tri à 20000 lignes (mon classeur en a un peu moins de 11000 ce qui me laisse de la marge...).

Et suite à tes remarques précédentes et celles d'Eric Jean, je me demandais si je n'allais pas effectivement tenter une installation en 32 bits. Si je ne trouve pas rapidement, c'est ce que je ferai.

Sais-tu pourquoi ça marche parfaitement en mode Excel sans échec ? Qu'est-ce qu'il a de moins (ou de plus ?)

Un grand merci.

Re,

Parmi toutes ces procédures, une est lente ou elles le sont toutes ?

Sinon, quelle est l'utilité de tous ces noms définis ?

Cdlt.

Re,

Sais-tu pourquoi ça marche parfaitement en mode Excel sans échec ? Qu'est-ce qu'il a de moins (ou de plus ?)

Ton pc tourne avec une config réduite. Pas de carte graphique, etc.

Cdt

Jean-Eric a écrit :

Re,

Parmi toutes ces procédures, une est lente ou elles le sont toutes ?

Sinon, quelle est l'utilité de tous ces noms définis ?

Cdlt.

Le tri du classeur par la première colonne bloque. C'est une date qui est une formule max de 2 colonnes dates.

La procédure associée est la Tri4_DatStaTit.

J'ai maintenant compris qu'il ne servait à rien dans mon contexte de nommer les colonnes.... je vais les enlever

Re,

Dans le fichier que j'ai joint, remplace la procédure Sub Main par celle-ci.

Et teste, sur le petit fichier puis sur une copie de ton original.

Cdlt.

Private Sub Main()
Dim wb As Workbook
Dim ws As Worksheet
Dim tbl As Variant, tbl2 As Variant
Dim i As Long

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("base")
    With ws
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rngData = .Cells(1).Resize(lastRow, lastCol)
        Application.Run ("modShowAllData.ShowAllRecords"), ws
        tbl = .Cells(2, 44).Resize(lastRow - 1)
        tbl2 = .Cells(2, 52).Resize(lastRow - 1)
        ReDim arr(1 To lastRow - 1)
        For i = 1 To lastRow - 1
            arr(i) = Application.Max(tbl(i, 1), tbl2(i, 1))
        Next i
        .Cells(2, 1).Resize(lastRow - 1).Value = Application.Transpose(arr)
    End With

End Sub

bonjour Jean-Eric

J'ai essayé avec la nouvelle procédure main, avec mon extrait puis une copie du classeur complet.

Le soucis n'est pas résolu mais voilà ce que j'ai constaté : sur l'extrait, si je lance successivement plusieurs procédures de tri, la Tri4 est systématiquement plus lente lorsqu'elle passe après la Tri2, j'ai essayé plusieurs fois.

Avec le fichier complet, c'est évidemment pire puisque cela bloque Excel pour un temps très très long. La Tri4 n'est pas la seule concernée, j'ai vu que la procédure SouhaitAchat_priorité réagit pareil.

C'est compliqué...

Pourras-tu me dire ce que fais exactement la procédure main que tu as modifiée ? je comprends qu'elle traite des colonnes de dates 1, 44 et 52...

Cdt

Patrick

Bonjour,

Je te propose de continuer avec ton vrai classeur.

Si tu es d'accord, je t'envoie mon courriel perso par MP.

Cdlt.

ok, il faut juste que je l'anonymise avant.

Comment puis-je te le transmettre ?

C'est très sympa le temps que tu m'accordes, merci beaucoup

Re,

Je t'ai envoyé un MP.

@ddetp88,

Je mettrai mes commentaires sur l'analyse du fichier de PatrickJack !...

Cdlt.