Discussion :

[Gallinette59]

Bonsoir à tous,

Je suis à la recherche d'une piste pour optimiser un code.

Pour un besoin métier, j'ai met en œuvre des listes dynamiques.
Sur chaque ligne d'un tableau d'environ 20.000 lignes, selon une sélection de paramètres sur 3 colonnes, je propose une liste adaptée.

En appliquant la formule qui suit, je mets à jour 1 à 1 la "validation de données" des lignes de mon tableau.
NB : Mon tableau évolue dans le temps avec des lignes qui apparaissent et d'autres qui disparaissent.
Hélas cette méthode prend du temps et je cherche des astuces pour faire la même chose mais plus vite (la MAJ prend pas loin de 5min à date et je serai amené à développer encore mon fichier).

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
 
Worksheets("MonOnglet").Activate
 
'On désactive la MAJ de l'écran
Application.ScreenUpdating = False
 
Application.Calculation = xlManual
 
For i = 2 To dernLigne_MonOnglet
 
 
        With Sheets("MonOnglet").Range("Y" & i).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:= _
            "=OFFSET(CATALOGUE!$E$1,MATCH(CONCATENATE(LEFT(V" & i & ",2),""_"",W" & i & " ,""_"",X" & i & "),CATALOGUE!$S$2:$S$100,0),0,COUNTIF(CATALOGUE!S:S,CONCATENATE(LEFT(V" & i & ",2),""_"",W" & i & ",""_"",X" & i & ")),1)"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
 
Next
 
 
'On réactive la MAJ de l'écran
Application.ScreenUpdating = True
 
Application.Calculation = xlAutomatic

J'ai fait des tests en remplaçant la formule par la création de liste et l'utilisation de la fonction "Indirect" mais je n'observe pas de gain de temps.

Merci par avance pour votre aide.

Cordialement

[Philippe Tulliez]

Bonjour,
Je n'ai pas vérifié la formule mais j'imagine que c'est la même sur l'ensemble des lignes.
Il n'y a donc pas de raison de passer par une boucle. Il suffit de sélectionner la plage complète et appliquer la Validation de données en une seule instruction.
Ce sera un gain de temps appréciable

[EDIT]
Une exemple du placement d'une liste dans la plage Y2:Y20000 de la feuille nommée MonOnglet du classeur où se trouve le code VBA sans boucle et c'est instantané.
Si je peux donner un petit conseil, il est préférable d'utiliser les tableaux structurés et les plages nommées.
Pour moi sauf cas exceptionnel où l'on doit automatiser des listes dans plusieurs classeurs, je ne vois pas l'intérêt d'utiliser le VBA pour faire de la validation de données. Toutes cellules d'un tableau structuré contenant une Validation de données, une mise en forme conditionnelle ou une formules est propagée automatiquement au fur et à mesure que l'on ajoute des lignes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
  Dim sht As Worksheet
  Dim rng As Range
 
  Set sht = ThisWorkbook.Worksheets("MonOnglet")
  Set rng = sht.Range("Y2:Y20000")
  With rng.Validation
  .Delete
  .Add Type:=xlValidateList, _
             AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, _
             Formula1:="=lst_Pays"
  End With
  Set sht = Nothing: Set rng = Nothing

[Gallinette59]

Bonsoir,

Un grand merci car je ne pensais pas (conceptuellement parlant) que cela était possible.
Effectivement, quelle rapidité !

Je te remercie également pour ce partage de conseils.
Je n'ai pas exploité ni les tableaux structurés, ni les listes par méconnaissances.

Cela pourra m'apporter des options pour les prochaines fois !

Cdt