Merging two excel named ranges into one

[kdshaw01]

Hello,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have been reading several thread about using the Union Method to merge two ranges into one, but I have not been able to successfully get mine to work. I read about issues with nulls, but fail to understand the issue. <o></o>
<o></o>
Here is what I have. Both ranges contain only text values with the exception of the trailing cells. I have tried removing the trailing cells to see if the union would work with no success<o></o>
<o></o>

Named Range Alpha is defined from A1 to A66. <o></o>
Cell A66 is blank.
<o></o>

Named Range Beta is defined from B5 to B25.<o></o>
Cell B25 is blank.<o></o>
<o></o>

When I run the step below in vba, the new named range is created, but contains nothing.<o></o>
<o></o>

ActiveWorkbook.Names.Add Name:="CombinedRoadmapFiles", RefersTo:=Union(Range("Alpha"), Range("Beta"))<o></o>
<o></o>

If I replace Beta with Alpha as shown below, the Alpha range is populated in the new named range. <o></o>
<o></o>

ActiveWorkbook.Names.Add Name:="CombinedRoadmapFiles", RefersTo:=Union(Range("Alpha"), Range("Alpha")).


Any advice is greatly appreciated.
<o></o>

Kind Regards,<o></o>
<o></o>
Kieran Shaw<o></o>

 

[BiocideJ]

When I run your code, I accurately get the combined Range as intended. What exactly are you trying to do with the range that is not working?

 

[kdshaw01]

I am trying to use the new combined range in a form field as a drop down. As I respond, I realize that I need to add additional details; my apologies. In the Excel Name Manager, I do see the new range and the explicate cell ranges from both Alpha and Beta. It is when I plug the new range into the form.field's RowSource = "'Data'!CombinedFiles" and I run the form. When I open the drop down field, the field is empty.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
..<o></o>
frmInsertProductDetailRow.fldCurrentProgramFileNamesList.RowSource = "'Data'!CombinedFiles"<o></o>
frmInsertProductDetailRow.fldCurrentProgramFileNamesList.Style = fmStyleDropDownList<o></o>
..<o></o>

 

[kdshaw01]

I'm going to close this thread. I figured out what was happening. The Union was working correctly as verified by Board member BiocideJ. Thank you. The root cause was that a form field’s row source property does not accept joined ranges; even if one uses a named range created from a union of named ranges. I believe that Excel breaks the named range back down to its definition at run time.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
RowSource = 'Sheet1'!$A$4:$A$49,'Sheet1'!$B$14:$B$58.<o></o>
<o></o>
My work around was to manually create a single named range from the two named ranges by looping through both ranges and populating a new set of cells and then creating a third name range based on the new data set. <o></o>
<o></o>
I hope this may help someone and or someone may clarify any misunderstandings that I may have.<o></o>
<o></o>
Thanks,<o></o>
<o></o>
Kieran Shaw<o></o>