Trying to set validation range using string yields error
 

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

Trying to set validation range using string yields error
 

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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

Trying to set validation range using string yields error
 

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

"Tom Ogilvy" wrote in message
...
xlBetween, Formula1:="=$AD$2:$AD" & EndCol

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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