Creating A Dynamic Named Range

[Ark68]

Hi have this code which (i think should) create a named range based on a dynamic range of cells.

rng_dsr = .Range("A2:A" & drow)
ThisWorkbook.Names.Add Name:="nr_dsr", RefersTo:=rng_dsr

When I run my code, I reference the Excel's 'Name Manager', and see that the name exists (nr_dsr). However, unlike my other named ranges in the workbook, the Value simply shows {...}, and Refers To shows ={"value1";"value2";"v...}. Are these expected results? If not, have I created my named range wrong?

I want to use this named range as the source of a combobox dropdown. My next question though, if my named ranged (nr_dsr) is correct, how can I append unique values to the combobox list in addition to the values of the named ranged. I wish to add "NA" and "NR" to the list created by the contents of the named ranged in my combobox dropdown.

 

[Joe4]

You have to use the "Set" command when defining ranges, i.e.

Set rng_dsr = .Range("A2:A" & drow)

 

[Ark68]

Oh yeah ... thanks! That made a difference.

So, now I have a "base" named range consisting of a dynamic range of cells. This named range serves as the base of several different dropdown validation lists (my original post inaccurately says combobox) for different ranges of cells. Each different range of cells will have a unique variation of named range 'nr_dsr'. For example, the validation list for cells in range A2:A10 will consist of the values in nr_dsr plus the values "NA" and "NR"

How do I add these two values to the validation list without changing 'nr_dsr' which needs to remain static for other validation lists? Would I have to build a new named range (nr_dsr_colA) for validation reference? If so, how do I add values to 'nr_dsr' to create 'nr_dsr_colA'?

With Range("CA:A10").Validation
   .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:="=nr_dsr", _  'plus NR and NA
    AlertStyle:=xlValidAlertStop
   .ErrorTitle = "Property"
   .ErrorMessage = "Please select a crew from the list"
End With

 

[Joe4]

As I see it, you have two options:
1. Build a new range that has all the options you want, and use that
- or -
2. Create a static list, looping through your range to add each value from the range and then add the other two values

 

[Ark68]

Thank you Joe4!

 

[Joe4]

You are welcome.