How to combine separate columns in to single range

**is49460** · 05-20-2011, 09:55 AM

Hello,

I have a range a1:a5 and d1:d5. I need to define both columns under a single named range(myRange). Currently, if I do so and then try to use it, for example: =columns(myRange) I'd expect to have "2" returned, but instead I get an error. Please help how can I define such range to use it as a single array.
Thanks!!

**sweep** · 05-20-2011, 10:17 AM

Hi,

Your array is defined as you require.

=COLUMNS() will return an error for a non-continuous range (ie, two or more separate columns)

**is49460** · 05-20-2011, 10:20 AM

Originally Posted by sweep

Hi,

Your array is defined as you require.

=COLUMNS() will return an error for a non-continuous range (ie, two or more separate columns)

Thanks for your reply! Is there a way to define it as a continious range? I'm trying to use it as a data source for a listbox, but the listbox doesn't like it either having a non-continious range.

**MarvinP** · 05-20-2011, 10:26 AM

Hi is49460 and welcome to the forum,

I guess the answer to your question is "That's the way it works!".

Your named range of myRange isn't a single array but multiple arrays. You can do functions like: Sum(myRange) and get good answers but Columns() needs a single array instead of multiple arrays. Throwing in a named range works only if the named range is a single array.

**is49460** · 05-20-2011, 10:35 AM

Originally Posted by MarvinP

Hi is49460 and welcome to the forum,

I guess the answer to your question is "That's the way it works!".

Your named range of myRange isn't a single array but multiple arrays. You can do functions like: Sum(myRange) and get good answers but Columns() needs a single array instead of multiple arrays. Throwing in a named range works only if the named range is a single array.

That makes sense. Although, I was hoping there is a function of some sort that would take multiple ranges and return a single one... but i guess I'm out of luck. Thanks again!

**MarvinP** · 05-20-2011, 10:45 AM

I think you were on the right track using named ranges. It just works out that named ranges can't be used in all formulas, depending on what the named range defines.