Get 1D Array using Evaluate

[YasserKhalil]

Thanks a lot.
I tried to test it like that

Sub Test()
    Dim a, x As Long
    
    x = 1000000
    a = InverseArray(Evaluate("ROW(1:" & x & ")"))
End Sub

But I didn't get 1D array ..and using the trick of column wouldn't be useful at this case ..
Can you post example of how to use it, please?

 

[Marc L]

Ok but later in the evening as I have to change computer …​

​

 

[YasserKhalil]

Take your time. I am not in hurry at all.

 

[Marc L]

You well used it !​

Sorry, my bad - maybe a bit tired - as InverseArray just swaps the dimensions but stays in 2D …​

​

As INDEX worksheet function has the same limitation as TRANSPOSE function,​

at this time you must use a Dim statement to create the 1D array and a For … Next loop to allocate its values​

only if the array upper limit is superior to 65 536, if not use the TRANSPOSE worksheet function​

so you can combine both ways - maybe the 3 ways adding COLUMN - in your own function VBA procedure.​

​

 

[YasserKhalil]

Thank you very much for this great information
Best and Kind Regards

 

[Marc L]

As allocating an array via a loop is fast enough so don't bother to combine different ways within a function …​

​

 

[YasserKhalil]

OK I will do my tutor.