Excel 2016 - VBA - Validation des données + boucle For : Recherche d'optimisation

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:

---

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

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:

---

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

---

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