Arrays: Transpose and the 255 length limit

[polarisking]

I have about 8,000 rows with 94 columns. I read the entire range into an array, then process the array 1 by 1 writing out to another array until I hit the end. Based on logic applied to one of the fields in the array, I may end up replicating one row in the first array multiple time into the second array.

So far, so good. Because the row count of the second array is always >= the row count of the first array, I need to REDIM the second array - but I cannot because you can only REDIM the last dimension, right? So, I write the first array into a transposed version of the first, then at the end I transpose the second so it comes out looking like the original 94 column format. Should work, right? Evidently, there's a known bug with TRANSPOSE that "breaks" when any field's length > 255. I cannot lose any of the contents of these "long" fields.

How do I get the second array to a size (# of actual rows) where I can move it to a range without having to transpose and consequently end up with the TYPE MISMATCH 13 error? Row 21 in Sheet1 is the offender - Column AE.

 

[Debraj]

Hi @polarisking

* Worksheets("Sheet2").Range("A1:CP" & NewLastRow) = Application.Transpose(OutputArray)
Whenever work with array, be careful with the Dimension.. Your OutputArray's dimension is {94 * (21+1000)}

ReDim OutputArray(LastColumn, LastRow + 10000)

But where your are placing is (94 * 21),

Either change the dimension of OutputArray to 94 * 21... just before pasting, change the dimemsion.
ReDim Preserve OutputArray(LastColumn, LastRow)

OR.. increase the array size in destination area.. like below
Worksheets("Sheet2").Range("A1:CP" & NewLastRow + 1000) = OutputArray

 

[NARAYANK991]

Hi ,

I am not sure what the problem really is ; you are getting an error at the following statement :

Worksheets("Sheet2").Range("A1:CP" & NewLastRow) = Application.Transpose(OutputArray)

This prepares the OutputArray so that the movement of data can be done in one statement without resorting to a loop.

My point is , if you do use a loop ( two loops in fact , one within the other ) to transfer the data back to the worksheet , how much time does it take ? Is it unacceptable ?

Narayan

 

[polarisking]

Thank you for taking the time to look at this. Here's a modification to the macro. Since I don't know how many rows I'll be writing out, I'm forced (I think) to over allocate at the beginning then REDIM right before I dump the array. It is true that you cannot REDIM the first dimension? Thinking that, I did the TRANSPOSE. Now, in this version I'm not - the dimensions appear to match - but the REDIM is failing on SUBSCRIPT out of range. It's the PRESERVE option of the REDIM that's preventing me from lowering the ROW values.

 

[NARAYANK991]

Hi ,

I really do not know what is the problem ; why do you have to REDIM before you transfer data from the array to the range ?

Depending on the size of the range , either enough data or insufficient data will be transferred.

Just comment out the offending statement , and see what you get.

Narayan

 

[polarisking]

Narayan, if I dump the non-REDIMed array, I end up with #N/A values in all the cells I didn't fill during the loop. It seems like it should be a common issue: not knowing in advance how many rows one will be writing, having to over allocate in advance, then not wanting to end up with the #N/A values due to the over allocation. I could, of course, do this exercise moving data from range to range, but I like the challenge of optimizing for speed.

 

[NARAYANK991]

Hi ,

I commented out the following statement :

ReDim Preserve OutputArray(NewLastRow, LastColumn)

and ran the procedure ; neither did the error come up , nor did I see any cells populated with #N/A values. Can you clarify ?

Narayan

 

[polarisking]

Narayan,

Well, don't I feel stupid! What happened to the other 1,000 unused rows? If I were up against memory constraints, wouldn't I have an issue with Excel allocating space it would never use (theoretical question, really)?

 

[NARAYANK991]

Hi ,

The issue of #N/A is now clear ; these values will be inserted into the worksheet cells only if the array is smaller than the matrix of worksheet cells ; thus if you try to move an array which has 20 rows and 10 columns into a range of 20 rows and 20 columns , the 10 columns after the 10th i.e. columns 11 to 20 will all be filled up with #N/A values.

The same goes for an array which has lesser number of rows than the worksheet range which you are trying to populate from the array. The extra rows will all have #N/A values.

Narayan