Macro validation de donnée avec cellule variable

Bonjour,

Je me permets de vous écrire concernant un problème que je rencontre lors d'un développement d'une brique de macro.
En effet, je voudrais avec une macro généré une liste de validation de donnée.

J'ai déjà fabriquer mon code,
Cependant, lors de l'enregistrement de la feuille et de sa fermeture/ouverture excel me génère une erreur :

"Désolé... Nous avons trouvé un problème dans le contenu de <NOM.xlsm>, mais nous pouvons essayer de récupérer le maximum de contenu. Si la source de ce classeur est fiable, cliquez Oui."

Forcement quand je clique sur oui, il me supprime mes listes de validation de donnée qui son relié à de la rechercheV etc...
Pendant mes investigations sur le soucis, je me suis rendu compte que le fait d'avoir les éléments rentrés en dur dans la liste de validation de donnée provoquait le problème.
Cependant, si je rentrais la plage de cellule en formule dans la liste aucun soucis. (exemple premier menu déroulant =CC ! $B$4:$B$5)

Il me faudrait l'aide d'une boucle complémentaire pour aller chercher ma plage de cellule suivant mes cas de figure (donnée variable)
Par la suite, j'ai une boucle qui s'occupe de regarder cas par cas pour supprimer les lignes en doublons (Concaténer)

Malheureusement je sèche sur la façon de l'incorporer dans la boucle et j'aurai besoin de l'expérience des membres présent sur ce forum.
Merci beaucoup de m'avoir lu, j'espère avoir été le plus clair possible.

O'neill

Pièce jointe 381611 (Excel 2016)
Pièce jointe 381615 (Excel 97/2003)