Drop down list shrinks as you go down column

I'm trying to build a drop down list using the values in a range of cells on
the same sheet. I Select about 150 cells and then using the Add method of
the Validation object to create the validation on this range of cells. The
code runs and all 150 cells now have a drop down list. The problem is that
the drop down list for the top cell in the range has a complete list of
values. As you go down the list of cells and click the drop down list, the
list of values gets shorter and shorter until finally it's blank when you get
about half way down the list of 150 cells.

The point at which the drop down list comes up blank seems to correlate to
the number of cells in the range used as the Formula1 in .Add method.

anyone seen this before or know what's going on? Love some suggestions.

thanks

--
D.Farns

Hi

Let's assume you have list values in range X1:X10
You defined your list using relative reference like
=$X1:$X10

Now, on row 2 the reference will be
=$X2:$X11
, on row 3
=$X3:$X12
, on row 11
=$X11:$X20

As you can see, the referred range 'moves off' the range with your list. To
avoid this, use absolute reference instead
=$X$1:$X$10


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"D.Farns" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to build a drop down list using the values in a range of cells
> on
> the same sheet. I Select about 150 cells and then using the Add method of
> the Validation object to create the validation on this range of cells.
> The
> code runs and all 150 cells now have a drop down list. The problem is
> that
> the drop down list for the top cell in the range has a complete list of
> values. As you go down the list of cells and click the drop down list,
> the
> list of values gets shorter and shorter until finally it's blank when you
> get
> about half way down the list of 150 cells.
>
> The point at which the drop down list comes up blank seems to correlate to
> the number of cells in the range used as the Formula1 in .Add method.
>
> anyone seen this before or know what's going on? Love some suggestions.
>
> thanks
>
> --
> D.Farns

Hi

Let's assume you have list values in range X1:X10
You defined your list using relative reference like
=$X1:$X10

Now, on row 2 the reference will be
=$X2:$X11
, on row 3
=$X3:$X12
, on row 11
=$X11:$X20

As you can see, the referred range 'moves off' the range with your list. To
avoid this, use absolute reference instead
=$X$1:$X$10


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"D.Farns" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to build a drop down list using the values in a range of cells
> on
> the same sheet. I Select about 150 cells and then using the Add method of
> the Validation object to create the validation on this range of cells.
> The
> code runs and all 150 cells now have a drop down list. The problem is
> that
> the drop down list for the top cell in the range has a complete list of
> values. As you go down the list of cells and click the drop down list,
> the
> list of values gets shorter and shorter until finally it's blank when you
> get
> about half way down the list of 150 cells.
>
> The point at which the drop down list comes up blank seems to correlate to
> the number of cells in the range used as the Formula1 in .Add method.
>
> anyone seen this before or know what's going on? Love some suggestions.
>
> thanks
>
> --
> D.Farns

Arvi, thanks for the quick response. I ended up addressing the problem by
looping through the range of cells and adding the drop downs one at a time
rather than adding to the selected range. Not sure which is more efficient,
but I suspect looping is better programming practice than selecting a range
anyway.

It's nice to know what was causing the issue none the less. Being cognisant
of when relative vs absolute references make a difference is essential.
Thanks for the reminder!
--
D.Farns


"Arvi Laanemets" wrote:

> Hi
>
> Let's assume you have list values in range X1:X10
> You defined your list using relative reference like
> =$X1:$X10
>
> Now, on row 2 the reference will be
> =$X2:$X11
> , on row 3
> =$X3:$X12
> , on row 11
> =$X11:$X20
>
> As you can see, the referred range 'moves off' the range with your list. To
> avoid this, use absolute reference instead
> =$X$1:$X$10
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
> "D.Farns" <[email protected]> wrote in message
> news:[email protected]...
> > I'm trying to build a drop down list using the values in a range of cells
> > on
> > the same sheet. I Select about 150 cells and then using the Add method of
> > the Validation object to create the validation on this range of cells.
> > The
> > code runs and all 150 cells now have a drop down list. The problem is
> > that
> > the drop down list for the top cell in the range has a complete list of
> > values. As you go down the list of cells and click the drop down list,
> > the
> > list of values gets shorter and shorter until finally it's blank when you
> > get
> > about half way down the list of 150 cells.
> >
> > The point at which the drop down list comes up blank seems to correlate to
> > the number of cells in the range used as the Formula1 in .Add method.
> >
> > anyone seen this before or know what's going on? Love some suggestions.
> >
> > thanks
> >
> > --
> > D.Farns
>
>
>

Hi

Btw., why donīt you like named range as validation list source? I myself use
validation lists quite frequently - and mostly with named range as source
(and there again mostly dynamic named ranges). In Excel, less code is almost
always a best solution.


Arvi Laanemets



"D.Farns" <[email protected]> wrote in message
news:[email protected]...
> Arvi, thanks for the quick response. I ended up addressing the problem by
> looping through the range of cells and adding the drop downs one at a time
> rather than adding to the selected range. Not sure which is more
efficient,
> but I suspect looping is better programming practice than selecting a
range
> anyway.
>
> It's nice to know what was causing the issue none the less. Being
cognisant
> of when relative vs absolute references make a difference is essential.
> Thanks for the reminder!
> --
> D.Farns
>
>
> "Arvi Laanemets" wrote:
>
> > Hi
> >
> > Let's assume you have list values in range X1:X10
> > You defined your list using relative reference like
> > =$X1:$X10
> >
> > Now, on row 2 the reference will be
> > =$X2:$X11
> > , on row 3
> > =$X3:$X12
> > , on row 11
> > =$X11:$X20
> >
> > As you can see, the referred range 'moves off' the range with your list.
To
> > avoid this, use absolute reference instead
> > =$X$1:$X$10
> >
> >
> > --
> > Arvi Laanemets
> > ( My real mail address: arvi.laanemets<at>tarkon.ee )
> >
> >
> > "D.Farns" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm trying to build a drop down list using the values in a range of
cells
> > > on
> > > the same sheet. I Select about 150 cells and then using the Add
method of
> > > the Validation object to create the validation on this range of cells.
> > > The
> > > code runs and all 150 cells now have a drop down list. The problem is
> > > that
> > > the drop down list for the top cell in the range has a complete list
of
> > > values. As you go down the list of cells and click the drop down
list,
> > > the
> > > list of values gets shorter and shorter until finally it's blank when
you
> > > get
> > > about half way down the list of 150 cells.
> > >
> > > The point at which the drop down list comes up blank seems to
correlate to
> > > the number of cells in the range used as the Formula1 in .Add method.
> > >
> > > anyone seen this before or know what's going on? Love some
suggestions.
> > >
> > > thanks
> > >
> > > --
> > > D.Farns
> >
> >
> >

Arvi, no reason really. I'm still a bit of a "newby" in the Excel object
model. I'm using named ranges elsewhere and found to be a pain redefining
the name when it changes is size. I do see that using Names is
preferred/recommended often. I'll start to use more often as they appear to
have benefits I could use. Perhaps they'll help with another issue I'm
having with AdvancedFilter. Just posted it.

--
D.Farns


"Arvi Laanemets" wrote:

> Hi
>
> Btw., why donÂīt you like named range as validation list source? I myself use
> validation lists quite frequently - and mostly with named range as source
> (and there again mostly dynamic named ranges). In Excel, less code is almost
> always a best solution.
>
>
> Arvi Laanemets
>
>
>
> "D.Farns" <[email protected]> wrote in message
> news:[email protected]...
> > Arvi, thanks for the quick response. I ended up addressing the problem by
> > looping through the range of cells and adding the drop downs one at a time
> > rather than adding to the selected range. Not sure which is more
> efficient,
> > but I suspect looping is better programming practice than selecting a
> range
> > anyway.
> >
> > It's nice to know what was causing the issue none the less. Being
> cognisant
> > of when relative vs absolute references make a difference is essential.
> > Thanks for the reminder!
> > --
> > D.Farns
> >
> >
> > "Arvi Laanemets" wrote:
> >
> > > Hi
> > >
> > > Let's assume you have list values in range X1:X10
> > > You defined your list using relative reference like
> > > =$X1:$X10
> > >
> > > Now, on row 2 the reference will be
> > > =$X2:$X11
> > > , on row 3
> > > =$X3:$X12
> > > , on row 11
> > > =$X11:$X20
> > >
> > > As you can see, the referred range 'moves off' the range with your list.
> To
> > > avoid this, use absolute reference instead
> > > =$X$1:$X$10
> > >
> > >
> > > --
> > > Arvi Laanemets
> > > ( My real mail address: arvi.laanemets<at>tarkon.ee )
> > >
> > >
> > > "D.Farns" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I'm trying to build a drop down list using the values in a range of
> cells
> > > > on
> > > > the same sheet. I Select about 150 cells and then using the Add
> method of
> > > > the Validation object to create the validation on this range of cells.
> > > > The
> > > > code runs and all 150 cells now have a drop down list. The problem is
> > > > that
> > > > the drop down list for the top cell in the range has a complete list
> of
> > > > values. As you go down the list of cells and click the drop down
> list,
> > > > the
> > > > list of values gets shorter and shorter until finally it's blank when
> you
> > > > get
> > > > about half way down the list of 150 cells.
> > > >
> > > > The point at which the drop down list comes up blank seems to
> correlate to
> > > > the number of cells in the range used as the Formula1 in .Add method.
> > > >
> > > > anyone seen this before or know what's going on? Love some
> suggestions.
> > > >
> > > > thanks
> > > >
> > > > --
> > > > D.Farns
> > >
> > >
> > >
>
>
>

Hi


"D.Farns" <[email protected]> wrote in message
news:[email protected]...
> Arvi, no reason really. I'm still a bit of a "newby" in the Excel object
> model. I'm using named ranges elsewhere and found to be a pain redefining
> the name when it changes is size. I do see that using Names is

This is for what dynamic named ranges are used.

An example:
You have a list on sheet MyList (list header in cell A1, list values
starting from A2, without any gaps). Define named range Selections as
=OFFSET(MyList!$A$1,1,,COUNTA(MyList!$A:$A)-1,1)

Whenever you add entries to list, or remove some, the named range adjusts
automatically.



--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

I think I get it. I'll play around with. I can see where it would cut down
on code and worth while to use. Thanks very much.
--
D.Farns


"Arvi Laanemets" wrote:

> Hi
>
>
> "D.Farns" <[email protected]> wrote in message
> news:[email protected]...
> > Arvi, no reason really. I'm still a bit of a "newby" in the Excel object
> > model. I'm using named ranges elsewhere and found to be a pain redefining
> > the name when it changes is size. I do see that using Names is
>
> This is for what dynamic named ranges are used.
>
> An example:
> You have a list on sheet MyList (list header in cell A1, list values
> starting from A2, without any gaps). Define named range Selections as
> =OFFSET(MyList!$A$1,1,,COUNTA(MyList!$A:$A)-1,1)
>
> Whenever you add entries to list, or remove some, the named range adjusts
> automatically.
>
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
>