Data validation VBA

[Paul21]

Hi, The bellow code adds droplists in collumn F up to the least row but drop lists values are loosing one value on each row. Like in F2 - 6values, in F3-5values and so on. What I did wrong ?

Sub DocType_Drop_List()

With Range("F2:F" & Range("A2").End(xlDown).Row).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Sheet2!P2:P6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
End With


End Sub

 

[Fluff]

You need to lock the cell references in the formula like

Formula1:="=Sheet2!$P$2:$P$6"