[RESOLU] Simplifier un code pour + de rapidité

[richert90]

Bonjour,
J'ai un code qui permet de remplir des zones de listes d'un UserForm. Pour cela il y a différentes boucles qui passent ligne par ligne le tableau et remplit les zones de listes (en enlevant les doublons!). Toutefois, etant donné que ce tableau peut être assez grand, les boucles mettent du temps et donc l'exécution de la macro de remplissage des zones de listes est assez lentes. Auriez-vous des solutions pour améliorer ce code en terme de performance?

Merci d'avance,

Sub filtres()
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Call nb_lignes_tanspose 'Nombre de données dans le tableau transposé
    
    ''On annule tous les filtres déjà appliqués:
    With Sheets(2)
        If .FilterMode = True Then .ShowAllData
    End With
    
    'D'abord on renseigne les elements de la liste Board(sans doublons):
    Filtre.zl_board.Clear 'Nettoyage
    For Each a In Range("B2:B" & i1)
        If a.Value <> "" Then
            Filtre.zl_board.Text = a.Value
            If Filtre.zl_board.ListIndex = -1 Then Filtre.zl_board.AddItem a.Value
        End If
    Next a
    Filtre.zl_board.ListIndex = -1
    
    'On met les valeurs du calendrier à vide
    F_calendrier2dates.date_début.Value = ""
    F_calendrier2dates.date_fin.Value = ""
    
    'On renseigne les elements de la liste date day(sans doublons):
    Filtre.zl_date.Clear 'Nettoyage
    For Each b In Range("C2:C" & i1)
        If b.Value <> "" Then
            Filtre.zl_date.Text = b.Value
            If Filtre.zl_date.ListIndex = -1 Then Filtre.zl_date.AddItem b.Value
        End If
    Next b
    Filtre.zl_date.ListIndex = -1

    'Une fois les listes définies, on peut afficher le userForm
    Filtre.Show
    
    Application.ScreenUpdating = False 'Permet de gagner du temps
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = True
  
End Sub

Contenu de la fonction "nb_lignes_transpose":

Sub nb_lignes_tanspose()

    i1 = 1
    Sheets(2).Select
    
    Do
        i1 = i1 + 1
    Loop Until Cells(i1, 2).Value = ""
    i1 = i1 - 1 
    
End Sub

 

[néné06]

Re : Simplifier un code pour + de rapidité

Bonjour Richert90,

Déja remplacer ce code par:

Sub nb_lignes_tanspose()

    i1 = 1
    Sheets(2).Select
   
    Do
        i1 = i1 + 1
    Loop Until Cells(i1, 2).Value = ""
    i1 = i1 - 1
   
End Sub

i1 = Sheets(2).Range("B1").End(xlDown).Row

Cela évite une boucle longue !

A+

René

 

[richert90]

Re : Simplifier un code pour + de rapidité

Bonjour rené,
Merci pour votre réponse, en effet je l'ai fait, c'est toujours ça de gagné!
Maintenant j'aimerais encore faire mieux si possible en évitant les boucles "for each" pour remplir les zones de listes.

Merci d'avance,

 

[néné06]

Re : Simplifier un code pour + de rapidité

Re,

Si tu pouvais nous joindre ce fichier avec des données non confidentielles, cela serait beaucoup plus facile.

A+

René

 

[richert90]

Re : Simplifier un code pour + de rapidité

Voici un extrait du tableau et le userform avec les zones de listes remplies.
Ici je n'ai que 600 lignes donc la macro est rapide mais quand je l'utilise quand il y a plus de 400 000 lignes c'est autre chose...

 

[Herdet]

Re : Simplifier un code pour + de rapidité

Voici un extrait du tableau et le userform avec les zones de listes remplies.
Ici je n'ai que 600 lignes donc la macro est rapide mais quand je l'utilise quand il y a plus de 400 000 lignes c'est autre chose...

Bonjour,
Voici une solution avec 2 TCD, 4 noms définis et 4 lignes de codes à adapter bien sûr
Les TCD sont automatiquement triès et sans doublons
Page TCD
T_BOARD =DECALER(T_BOARD_ens;;;NBVAL(T_BOARD_ens))
T_BOARD_ens =TCD!$A$5:$A$50001
T_DATE =DECALER(T_DATE_ens;;;NBVAL(T_DATE_ens))
T_DATE_ens =TCD!$C$5:$C$50000

Sub filtres()
   Load Filtre
   Filtre.zl_board.RowSource = "T_BOARD"
   Filtre.zl_date.RowSource = "T_DATE"
   Filtre.Show
End Sub

Ou plus simple encore avec (fichier : RD2-Richert90.xlsm )

Sub filtres()
   Filtre.Show
End Sub

Private Sub UserForm_Initialize()
   zl_board.RowSource = "T_BOARD"
   zl_date.RowSource = "T_DATE"
End Sub

Cordialement
Robert

 

[néné06]

Re : Simplifier un code pour + de rapidité

Bonjour Herdet,

@Richert90
Regardes cette "Bidouille",
Test en temps et dis-moi ?

A+

René

 

[richert90]

Re : Simplifier un code pour + de rapidité

Re,

Merci Herdet et néné06 pour vos solutions,
En les comparant, j'ai conserver celle de néné06 car il est pour moi plus simple de ne pas utiliser de TCD.
Concernant le temps d'exécution avec 400 000 données, je suis passé de 23s (avec mon ancien code) à 5s avec celui de néné06 (à peu près équivalent avec celui de Herdet) donc ce qui est plutôt une belle progression

Merci à vous

 

[néné06]

Re : [RESOLU] Simplifier un code pour + de rapidité

Re,

Prendre cette version qui est plus efficace " il me semble" !

A+

René

 

[richert90]

Re : [RESOLU] Simplifier un code pour + de rapidité

Re néné06

J'ai essayé ta nouvelle version mais celle-ci met 11s si on a 400 000 lignes donc elle met environ 2 fois plus de temps que ta version précédente.
Tu pensais qu'elle était plus efficace en terme de temps d’exécution ou plus pour le côté pratique (car pas de copie de données dans une autre colonne?)?

 

[Herdet]

Re : [RESOLU] Simplifier un code pour + de rapidité

Richert90 ( et néné06)

L'utilisation de RemoveDuplicates et sort a été mon 1er test que j'ai vite abandonné au profit de TCD que je trouve plus efficaces car on peut les manipuler à volonté et même en avoir qu'un seul.

Richert90, ce que nous ignorons, ce sont les manipulations tu vas faire sur 1 000 000 de lignes après ces 2 petites listes.

Pour traiter un tel nombre de lignes (recherches, extraction, affichage,...), il vaudrait mieux utiliser des commandes SQL avec ADO sur le fichier de données fermé
Ensuite avec quelques Recorset sur le fichier fermé, on récupère ce que l'on veut :
List Board triée : SELECT DISTINCT [Board] FROM [BD_BASE] ORDER BY [Board] ASC;
List Dates triée : SELECT DISTINCT [Date] FROM [BD_BASE] ORDER BY [Date] ASC;

SELECT * FROM [BD_BASE] WHERE [Max value] ='54' ORDER BY [Date] ASC;
......

Je n'ai pas testé sur ton fichier mais c'est à voir suivant tes manipulations futures.
Robert

 

[richert90]

Re : [RESOLU] Simplifier un code pour + de rapidité

Re,

En effet l'utilisation de SQL pourrait être intéressant. Il faudrait juste que je réussisses à stocker le résultat des requêtes dans es zones de listes correspondantes.
Merci de l'info