Liste de validation avec des cellules

Bonjour,

Je dois créer une macro en vba qui permet de créer une liste de validation des données à partir d'une liste de cellules. Le problème c'est que ces cellules ne sont pas cote à cote et donc il n'y a pas moyen de le faire avec mon code actuel.
Pourriez-vous me dire quel est le code vba à utiliser dans ce cas là ?

Voici mon code actuel :
Sub Test()

Sheets(n).Select
Sheets(n).Activate

ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=2

Range("C37:C47,N37:N47").Select
Range("N37").Activate
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$C$330:$C$346"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Range("D19:D24,I19:I24,O19:O24").Select
Range("O19").Activate
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$BU$90:$BU$91"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = _
"Merci de saisir d'abord les %, puis les % Cas, et les € en dernier"
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("Z46").Select

Range("AL37:AL47,AW37:AW47").Select
Range("AW37").Activate
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$AL$330:$AL$346"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Range("AX34").Select

Range("BU37:BU47,CF37:CF47").Select
Range("CF37").Activate
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$BU$213:$BU$235"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Range("BV19:BV24,CA19:CA24,CG19:CG24").Select
Range("CG19").Activate
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$BU90:$BU$91"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("BV31:BV34,CA31:CA34,CG31:CG34").Select
Range("CG31").Activate
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$BU91:$BU$93"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With



Range( _
"BV37:BV47,CA37:CA48,CG37:CG48,CG55:CG56,CA55:CA56,BV55:BV56,BV59:BV60,CA59:CA60,CG59:CG60,CG77:CG79,CG70:CG74,CA70:CA74,BV70:BV74,BV77:BV79,CA77:CA79" _
).Select
Range("CA79").Activate
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$BU$91:$BU$93"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With



Next n
End Sub