Sub Macro1()
For i = 1 To 6
If Range("B" & i).Value <> "" Then
If IsEmpty(rng) = False Then
rng = rng & "," & Range("B" & i).Value
Else
rng = Range("B" & i).Value
End If
End If
Next i
Range("B11").Validation.Delete
Range("B11").Validation.Add Type:=xlValidateList, Formula1:=rng
End Sub
Sub Macro2()
For i = 1 To 6
If Range("B" & i).Value <> "" Then
rng = rng & Range("B" & i).Value & ","
End If
Next i
rng = Left(rng, Len(rng) - 1)
Range("B11").Validation.Delete
Range("B11").Validation.Add Type:=xlValidateList, Formula1:=rng
End Sub