Drop down list shrinks as you go down column

[Guest]

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

 

[Arvi Laanemets]

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

 

[Guest]

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!

 

[Arvi Laanemets]

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

 

[Guest]

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.

 

[Arvi Laanemets]

Hi

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.

 

[Guest]

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.

 

[Guest]

Arvi, thanks for posting this helpful 'trick' - a named range that adjusts
automatically! As one of the characters on the Guiness Stout television
commercials (running in California) would say: "BRILLIANT!!"