Data validation

[wdknisel]

Im trying to add some data validation into a cell then copy it through out the column. I would like to have a null or blank option to appear in the dropdown to be a viable option to select. in recording the macro I am using the following VB

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="1,2,3,4,5,6"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

how do i enable this Null or blank in the list above (1,2,3,4,5,6)?

 

[F&s anaylst]

Hi there,

xlBetween, Formula1:="1,2,3,4,5,6"

replace the above with this

xlBetween, Formula1:=",1,2,3,4,5,6," & Chr(160)

should work ok for you, put it at front of your array if you want it at top of drop down list

 

[wdknisel]

thanks Kevin. I made the suggested edits tot he formula however i am still only getting 6 values in the dropdown (1 2 3 4 5 6) am i missing somethign here?

 

[F&s anaylst]

sorry i put coma in wrong position the below will work for you now

xlBetween, Formula1:="1,2,3,4,5,6," & Chr(160)

 

[wdknisel]

Thanks kevin, should have pick up the comma. WORKS perfectly
Thanks again