arrays in VBA

The quickest easiest way to populate an array from a sheet is

Please Login or Register  to view this content.

change rows & columns to suit
this gives a 1 dimension array

If you need a 2 dimenstion array with the 2nd dimention blank have your data in column A & column B blank

Please Login or Register  to view this content.

jindon

Thanks for pointing out my error that my code gives a 1 dimension array which is incorrect.


This line of your code will cause incorrect

Please Login or Register  to view this content.

It should be

Please Login or Register  to view this content.

Some links on arrays

http://puremis.net/excel/code/053.shtml

http://www.dailydoseofexcel.com/arch...nge-using-vba/

http://www.exceltip.com/st/Array_var...Excel/509.html

Variant array to hold two arrays. Step throught this sample subroutine with the immediate window open

Please Login or Register  to view this content.

VBA Noob

To qualify Jindon's observation that "when you convert range value to vb array, it will be always 1 based 2D array, no exception", it must however be noted that when range transposition is involved, you can sort of convert range values to a 1D Array as the following codes illustrate.

1. The simple transposition of one-column range:

Please Login or Register  to view this content.

2. The double transposition scenarios

Please Login or Register  to view this content.

There is no doubt that it is this property of Array dimension changing by transposition from one-column range (2D) to putatively a one-row range (ID) that gives the illusion that "horizontal single row range" gives rise to a 1-D Array.

Outside the context of transposition, Jindon is perfectly right. I was only a bit uneasy about the rider "no exception". Again, as he pointed out, Variant arrays are by definition typecast to Base1.