Load only one dimension of an array

[polarisking]

I have an array, pre-defined, defined as 1 to 900, 1 to 20. Those 20 represent non-contiguous columns. I'd like to load the array in chunks by column (or groups of columns) rather than doing the standar r = 1 to x, c= 1 to y.

If the columns were contiguous, I could simply load the entire array with one statement: Arr = Range. Can I go column by column and load, for example, arr(,4) where all the elements for column 4 will be loaded and not touch anything else?

Thank you in advance.

 

[Hui]

var=application.WorksheetFunction.Index(Array/Range,Row No, Col No).value

If you leave Row No blank It will import the Column which is Col No. into Var

eg:
var = Application.WorksheetFunction.Index(Sheet1.Range("A1.M20"), 0, 4).Value

 

[NARAYANK991]

Hi ,

I don't think I have understood your requirement , but see if this helps :

http://stackoverflow.com/questions/...-multiple-non-contiguous-ranges-into-an-array

Narayan

 

[polarisking]

Sorry, folks, for my lack of clarity. Here's the example in the attached file. My objective is to move very large quantities of data from Tab1 to Tab2 based on a table of eligible columns. My thinking was that the the most efficient way would be to read the eligible (highlighted) columns into a table and the write the array to a resized range in Tab2. I'd like to loop through the list of columns and read each one into the next available dimension in the array - basically loading the array in pieces, but in chunks/columns, not individual cells.

Narayan, I'll take a look at the stackoverflow link.

 

[Hui]

See the attached file:
It will transfer the Columns where Row 1 is Yellow

 

[polarisking]

A very nice, elegant solution. Thank you. The column-by-column move is definitely a big performance boost from cell-by-cell.

I'm surmising that there's probably not a way to load a array dimension-by-dimension, then move the entire array over to the target area.

Example: In this case, one would step through the first row testing for colorindex value (or using whatever criteria one wishes to determine the column's eligibility) and load the array piecemeal with a statement something like

array(*,i) = Range(cells(1,columnpos),cells(lastrow,columnpos))

where "*" (I know I'm making this up this syntax) would effectively allow for all rows in dimension i to be loaded. This statement would, in my example, execute 6 times.

Again, thank you for your thoughtful solution.

 

[Hui]

VBA has limited built-in array handling procedures and although array handling functions can all be added to VBA by adding in extra subroutines/functions I don't think for your application here it is worth it.

There are several methods to solve your problem, some involve arrays, as I used, as per your request, other techniques would simply assign values or copy columns directly to the destination cells.

If you are really interested in doing this with arrays I'd refer you to these functions which expand the array handling of VBA
http://www.cpearson.com/excel/vbaarrays.htm

 

[Hui]

If you decide to use array, I would load the entire data set into an array
Then step through it deleting Columns that don't belong there, rather than adding them