Named Range to Array

[efernandes67]

Hi,

I am trying to populate one dimensional array with a named range. The named range "Names" refers to just one column. When I run the code below, I get a 2 dimensional array, why?

Please Login or Register  to view this content.

What do I have to change to make it work?

Regards,
Elio fernandes

[tigeravatar]

Elio,

Whenver you're loading an array directly from a cell range, the resulting array will always be 2-dimensional. The first dimension is the number of rows, the second dimension is the number of columns, eg: the resulting array will be Array(1 to rowcount, 1 to colcount). So even with a 1-column cell range, the resulting array will be 2-dimensional, the second dimension being 1 to 1 because there's only 1 column.

If it must be a one-dimensional array, you can use the following:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[snb]

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[tigeravatar]

@snb, i'm not sure what that accomplishes. If the named range "Name1" is in sheet1 cells A1:A10, the variable sn would be assigned the following string value: "=Sheet1!$A1:$A10". sn is not an array of any kind...

[efernandes67]

When I put this code, I was trying to avoid a loop to minimize the code.

Thanks, it works.

Elio Fernandes

[snb]

@tigeravatar

Both codes I posted will result in a 1-dimensional array that contains the values of A1:A10.

[tigeravatar]

@snb, I was able to get the second one to result in a 1-dimensional array successfully, so that's pretty cool. The first one just wouldn't do it, though. Ah well. As always, I learned something new again today

[snb]

@tigeravatar

Maybe the attachment will convince you. (see the macro in it)

[tigeravatar]

@snb, that's pretty slick, I see where i was going wrong with the first one now. Thanks again