PDA

View Full Version : [SOLVED] Resize Array



Roli001
11-04-2013, 05:43 PM
I have a large dataset in a spreadsheet that I import into an array named 'temptStamp' in VBA


Sub RSI()

Dim temptStamp As Variant
Dim tStamp() As Variant
Dim K As Integer

eRow = [a1000000].End(xlUp).row

set temptStamp = Range(Cells(1, 1), Cells(eRow, 7))




I then use a loop to move the 'temptStamp' data into an array named 'tStamp' with the same number of rows but added columns, which I use later in the code with various data.



ReDim tStamp(eRow, 30)

For i = 1 To eRow
For j = 1 To 7
tStamp(i, j) = temptStamp(i, j)
Next j
Next i

Set temptStamp = Nothing


Looping through the 'temptStamp' array to assign data to a larger dimension array is time consuming. Is there a way to resize the 'temptStamp' array to add 23 columns (8-30)? I have tried redim and redim preserve but I have not been successful so far.

Thanks

mikerickson
11-04-2013, 06:32 PM
Try this

Dim temptStamp As Variant
Dim tStamp As Variant: Rem changed line, variable is not an array **********
Dim K As Integer

eRow = [a1000000].End(xlUp).row

Set temptStamp = Range(Cells(1, 1), Cells(eRow, 7))

tStamp = temtStamp.Value
ReDim Preserve tStamp(1 To UBound(tSTamp,1), 1 To 30)

Note that Redim Preserve will only work on the last index of an array. You can't use it to increase the number of rows.

Also, arrays are not objects, setting them to Nothing won't necessarily work. For arrays, the Erase command is used
Erase myArray

Roli001
11-04-2013, 06:37 PM
Try this

Dim temptStamp As Variant
Dim tStamp As Variant: Rem changed line, variable is not an array **********
Dim K As Integer

eRow = [a1000000].End(xlUp).row

Set temptStamp = Range(Cells(1, 1), Cells(eRow, 7))

tStamp = temtStamp.Value
ReDim Preserve tStamp(1 To UBound(tSTamp,1), 1 To 30)

Note that Redim Preserve will only work on the last index of an array. You can't use it to increase the number of rows.

Also, arrays are not objects, setting them to Nothing won't necessarily work. For arrays, the Erase command is used
Erase myArray

Works Perrrrrfect!

Thank you.