Join non-contiguous ranges into one range via named range?

[ker_01]

I'm certain that I did this some years ago, but can't recall how.

I have two separate (non-contiguous) ranges that I need Excel to see as one
range so I can use it as the source for a data validation list.

I've tried the obvious, such as comma, semicolon, colon, ampersand, but
either my syntax is wrong, or none of those are the right way to do it.

Any suggestions?

Thanks!
Keith

 

[Gary''s Student]

This is one way to make a range out of two pieces:

Sub rangeRover()
Set r = Range("A1:A10,C1:C4")
r.Select
End Sub

 

[Dave Peterson]

Maybe you can create a contiguous range on another (hidden?) worksheet. You
could use formulas that point back to the original ranges. Then use this for
the data|validation list source.

Or maybe you could use code to apply the data|validation. The code would create
the comma separated list -- just like you typed it by hand.

But that would suffer the same restrictions (not too long and no commas in any
of the strings and case sensitive) that you get when you do it manually. And
the code would have to reapply the Data|Validation if you changed any of the
values in either range.

 

[Roger Govier]

Hi Keith

I put a coded solution to you problem at the end of your previous thread on
this matter.