Trying to set validation range using string yields error

  • Thread starter Thread starter Ed
  • Start date Start date
E

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
 
Back
Top