With ws
lngZeilemax = .Range("A" & .Rows.Count).End(xlUp).Row
For lngZeile = 9 To lngZeilemax
If Not .Cells(lngZeile, 1).Value = "" Then
lngZ = lngZ + 1
ReDim Preserve VarDat(lngZ)
VarDat(lngZ) = .Range("A" & lngZeile).Value
End If
Next lngZeile
End With
With ThisWorkbook.Worksheets("CodeFinder").Range("C5").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlEqual, Formula1:=Join(VarDat, ";")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Please Select a Value"
.ErrorTitle = ""
.InputMessage = "Please Select a Value"
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With