im having trouble figuring out what is wrong with this code....
it should use the range set to the variable ProdRange as the source for
the data, and putting the drop down list in cell A3.....

Dim ProdRange As Range
Set ProdRange = Range("a7", "a" & Range("EndOfProducts").Offset(-2,
0).Row)
Range("a3").Validation.Add xlValidateList, , , "=" &
ProdRange.Address

anyone know what im doing wrong?

Your problem is in this line:
Set ProdRange = Range("a7", "a" & Range("EndOfProducts").Offset(-2, 0).Row)
Maybe should be:
Set ProdRange = Range("a7":"a" & Range("EndOfProducts").Offset(-2, 0).Row)
"Nigel" wrote:

> im having trouble figuring out what is wrong with this code....
> it should use the range set to the variable ProdRange as the source for
> the data, and putting the drop down list in cell A3.....
>
> Dim ProdRange As Range
> Set ProdRange = Range("a7", "a" & Range("EndOfProducts").Offset(-2,
> 0).Row)
> Range("a3").Validation.Add xlValidateList, , , "=" &
> ProdRange.Address
>
> anyone know what im doing wrong?
>
>

What exactly is the problem you are experiencing? You might need to
delete the Validation right before you add it:

With Range("a3").Validation
.Delete
.Add ...
End With

Let me know if that helped.

Regards,
Steve

Nigel wrote:

> im having trouble figuring out what is wrong with this code....
> it should use the range set to the variable ProdRange as the source for
> the data, and putting the drop down list in cell A3.....
>
> Dim ProdRange As Range
> Set ProdRange = Range("a7", "a" & Range("EndOfProducts").Offset(-2,
> 0).Row)
> Range("a3").Validation.Add xlValidateList, , , "=" &
> ProdRange.Address
>
> anyone know what im doing wrong?

i dont really understand how i would apply that...

the problem is my drop down is showing duplicates and blank cells....

On Nov 17, 6:35 am, wisc...@googlemail.com wrote:
> What exactly is the problem you are experiencing? You might need to
> delete the Validation right before you add it:
>
> With Range("a3").Validation
> .Delete
> .Add ...
> End With
>
> Let me know if that helped.
>
> Regards,
> Steve
>
> Nigel wrote:
> > im having trouble figuring out what is wrong with this code....
> > it should use the range set to the variable ProdRange as the source for
> > the data, and putting the drop down list in cell A3.....
>
> > Dim ProdRange As Range
> > Set ProdRange = Range("a7", "a" & Range("EndOfProducts").Offset(-2,
> > 0).Row)
> > Range("a3").Validation.Add xlValidateList, , , "=" &
> > ProdRange.Address
>
> > anyone know what im doing wrong?

I see. I believe the data validation list always shows all values
within the given range, even if they are duplicates or blanks. You will
need to remove them from your ProdRange. There are a number of posts in
this group that address this specific issue. Specifically, search for
"prevent duplicate in data validation". The second post gives you some
good pointers.

Regards,
Steve

Nigel schrieb:

> i dont really understand how i would apply that...
>
> the problem is my drop down is showing duplicates and blank cells....
>
> On Nov 17, 6:35 am, wisc...@googlemail.com wrote:
> > What exactly is the problem you are experiencing? You might need to
> > delete the Validation right before you add it:
> >
> > With Range("a3").Validation
> > .Delete
> > .Add ...
> > End With
> >
> > Let me know if that helped.
> >
> > Regards,
> > Steve
> >
> > Nigel wrote:
> > > im having trouble figuring out what is wrong with this code....
> > > it should use the range set to the variable ProdRange as the source for
> > > the data, and putting the drop down list in cell A3.....
> >
> > > Dim ProdRange As Range
> > > Set ProdRange = Range("a7", "a" & Range("EndOfProducts").Offset(-2,
> > > 0).Row)
> > > Range("a3").Validation.Add xlValidateList, , , "=" &
> > > ProdRange.Address
> >
> > > anyone know what im doing wrong?