Union named Ranges

**MrHockey** · 04-29-2009, 05:58 PM

(Using Office 2003 on XP Pro) I have two named ranges that I want to union into one big named range so that I can use the big named range in a validation table. Unfortunately the big range does not appear on the list of named range so I switched to VBA to try and lick this but really I was hoping a non-VBA solution exists. Exploring possible VBA solutions, here is what I have so far:

Please Login or Register  to view this content.

This code works and BigRange is created but when I attempt to use it in a validation table an error states that the range is not contiguous (or something to that effect.).

Notice I used a worksheet ACTIVATE event. The validation is on Sheet2, the data for columns A & B are on Sheet1, by using the ACTIVATE event the code will refresh BigRange each time Sheet2 is activated which is necessary since the data on sheet1 can be changed at anytime.

Back to a non-VBA solution, I tried to concatenate two named ranges in the "source:" field of data validation dialog. I tried to input: =SmallRange1, SmallRange2 and excel reported this was no good and to use a delimiter but I cannot determine what the correct delimiter is. If a delimiter works then this would be a preferred solution over a VBA solution.

If there is no straightforward solution then I plan to write a VBA solution that builds one contiguous list from columns A and B and then saves a named range from that. But I want to hold off and see if anyone has a nicer solution.

Thanks for taking the time to read this!

**shg** · 04-29-2009, 07:57 PM

... but when I attempt to use it in a validation table ...

First, if you mean data validation, it has to be a single row or column.

Second, there's no need to select the sheet to define the range, and no need for the implicit Evaluate function you're using:

Please Login or Register  to view this content.

Third, why such a large range, rather than a dynamic range that conforms to the size of your data?

**MrHockey** · 05-01-2009, 05:27 PM

Hi Shg and sorry for the slow reply.

Yes, I mean data validation and the ranges do not need to be that big, 65536 was just an example, hope that did not cause to much confusion.

But you answered my main question, there appears to be no way to stuff two non contiguous ranges into one range for use in a validation table. I guess this is just an arbitrary constraint of data validation. I'll go to plan B and have the activate event build one contiguous range from two (or more) non-contiguous ranges and update the named range. It would be nice if Excel 2007 could remove this constraint.

Thanks for your input, I really appreciate it. You never know until you ask...

**Jack in the UK** · 05-02-2009, 09:34 AM

This would be a reWrite for Your code, add to Your Worksheet copy

Please Login or Register  to view this content.

This might be better i feel (test on copy of your work just in case You end up with some funky results

Please Login or Register  to view this content.

jiuk

**Jack in the UK** · 05-02-2009, 09:37 AM

Please test on copy of Your work in case You end up with some funky results

This will Offset the two columns to each finish point

Please Login or Register  to view this content.

jiuk

**MrHockey** · 05-08-2009, 12:44 AM

Jack, once again I must apologize for the slow reply, we are in testing mode (by day) and I play hockey 3 times per week (by night)...

Thank you for the 3 code offerings. I opened a new workbook and created 3 modules with your example code. Then I created two headers for col 1 and 2 and input some junk data into these columns for testing. And col 5 has a header is is where the validation table is to be defined.

The 2nd and 3rd blocks of code did indeed union the two ranges into "AllEvent" but when I tried to use this named range in the definition of the validation table Excel reported, "The list source must be a delimited list, or a reference to single row or column."

I am not sure where to go with this. If you are able to build a validation table (please confirm) from AllEvent and you are using Office 2003 then maybe one of my Tools\Options settings needs attention. Do you have any suggestions? Just to be extra sure we are on the same page I will submit my test workbook after I post this. I'll call it AllEvent.xls

I like the way you defined RANGE_1 (and 2) using .End(xlUp) That's a good trick I'll have to remember.

I also want to thank you for your input thus far.

MrHockey in Seattle

**MrHockey** · 05-08-2009, 01:03 AM

Jack, once again I must apologize for the slow reply, we are in testing mode (by day) and I play hockey 3 times per week (by night)...

Thank you for the 3 code offerings. I opened a new workbook and created 3 modules with your example code. Then I created two headers for col 1 and 2 and input some junk data into these columns for testing. And col 5 has a header is is where the validation table is to be defined.

The 2nd and 3rd blocks of code did indeed union the two ranges into "AllEvent" but when I tried to use this named range in the definition of the validation table Excel reported, "The list source must be a delimited list, or a reference to single row or column."

I am not sure where to go with this. If you are able to build a validation table (please confirm) from AllEvent and you are using Office 2003 then maybe one of my Tools\Options settings needs attention. Do you have any suggestions? Just to be extra sure we are on the same page I will submit my test workbook after I post this. I'll call it AllEvent.xls

I like the way you defined RANGE_1 (and 2) using .End(xlUp) That's a good trick I'll have to remember.

I also want to thank you for your input thus far.

MrHockey in Seattle