Trying to set validation range using string yields error

[Ed]

I'm trying to set Data Validation by code. The whole thing worked fine as
long as I had a set range:
' Set validation
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$AD$2:$AD$93"
Because the list can grow with each update of my workbook, I need to detect
the length of this range and then set it. So I did this:
' Find end of validation range
EndCol = Range("AD65536").End(xlUp).Row

' Set validation
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$AD$2:$AD & EndCol"
I get "Application-defined or object-defined error". I'm assuming VBA
doesn't like the way I'm trying to define the range at Formula1:=. Any
suggestions?

Ed

 

[Tom Ogilvy]

xlBetween, Formula1:="=$AD$2:$AD" & EndCol

 

[Ed]

DuH! I knew that!! I just didn't remember it. Thanks, Tom.
Ed