How to convert a 1 by N array intoa one-dimensional array of length N?

[JenniferMurphy]

When I load a VBA array from a range like A5:J5, I get a 1x10 array. It's a bit of a nuisance to have to always include that extra dimension (Arr(1,i)) rather than just Arr(i).

Is there a simple way to convert it from 1x10 to just 10 without a loop?

Thanks

 

[James006]

Hi,
Have you tested : arr= Range("A5:J5")

 

[JenniferMurphy]

Hi,
Have you tested : arr= Range("A5:J5")

It's a 1x10, no?

 

[mohadin]

Hi
Try

arr = Application.Transpose(Application.Transpose(Range("A5:J5")))

 

[JenniferMurphy]

Hi
Try

arr = Application.Transpose(Application.Transpose(Range("A5:J5")))

Perfect, thank you!

 

[JenniferMurphy]

PS: I find it highly curious that one-dimensional ranges like "A2:A5" or "A2:D2" come into VBA as 2-dimensional arrays, but "A1" comes in not as a 1x1 array, but as a dimensionless scalar.

 

[mohadin]

Perfect, thank you!

You are Very welcome
And thankyou for the feedback
Be happy and safe

 

[JenniferMurphy]

You are Very welcome
And thankyou for the feedback
Be happy and safe

PS: I notice that this only works for single rows. Single columns remain 2 dimensional.

 

[RoryA]

Just use one Transpose for a single column.

 

[offthelip]

PS: I find it highly curious that one-dimensional ranges like "A2:A5" or "A2:D2" come into VBA as 2-dimensional arrays, but "A1" comes in not as a 1x1 array, but as a dimensionless scalar.

I totally agree and not just curious it adds extrra coding because you have to check to see if it is an array, it would be really nice is EXCEL made it an array if you define the variable as :

Dim myarray ()

and defined it as single item if defined as

Dim Myvariable

so we had the option!!