Creating A Dynamic Named Range

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,847
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi have this code which (i think should) create a named range based on a dynamic range of cells.

Code:
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.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
You have to use the "Set" command when defining ranges, i.e.
Rich (BB code):
Set rng_dsr = .Range("A2:A" & drow)
 
Solution

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,847
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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'?

VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 23, 2004
Messages
3,847
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you Joe4!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,718
Members
415,922
Latest member
gemmatay88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top