VBA - Data Validation for a whole column reference

[salman94]

Hi,

I need some help adding Data Validation in VBA. I would like it to reference a whole column in a separate worksheet but I get an error in the formula1 line. How do I fix this? I also tried ".Range("I:I")" but that also doesn't work.

Sub DataValidation()

Sheets("Results").Range("C15").Select​

​

With Selection.Validation​

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _​

Formula1:=Sheets("Pending Transactions Count").Range("$I$2:$I$300")​

.IgnoreBlank = True​

.InCellDropdown = True​

.InputMessage = "Select which month-end to catch transactions for"​

End With​

End Sub

 

[njimack]

You want your validation list linked to a column of a million rows?

 

[Fluff]

Try

Formula1:="='Pending Transactions Count'!$I$2:$I$300"

 

[salman94]

Hi, is there any other way to catch the various cells in that column?

 

[Fluff]

In what way?

 

[salman94]

Try

Formula1:="='Pending Transactions Count'!$I$2:$I$300"

Thanks! This worked! Is there any way to remove the duplicates in the validation list?