Resize Array

[Roli001]

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]

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]

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.