Data validataion with VBA/ Named Range

[ogo]

I was using the following code part for data validation
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$B$" & startRow & ":$B$" & endRow................

But the data and the drop down are in different sheets. So i had to try named range the variables startRow and endRow . But this is also not working.Is there any method take data from one sheet into drop down in other sheet .

Thanks,
Ogo

 

[ogo]

The code is actually adding the range as expected, but the the value in the cell range is not been added.

 

[ogo]

Finally I have fixed it. Don't really know what was the error. The code goes like this..


Sheet2.Range("B" & StartRow & ":" & "B" & EndRow).Name = "NamedRange"
Sheet5.Cells(iRow, iCol).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=NamedRange"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Thank u all,
ogo