Multi Dimensional Dynamic Array query

[hs2610]

I know below is possible -

Sub test()
Dim myArray As Variant
myArray = Sheets("Data").Range("A1:Z1").Value
End Sub

is it possible to ReDim Preserve myArray and insert a new row in one go as done above. My requirement is to make a Multidimensional dynamic array but want to insert the rows in one go.

 

[Fluff]

If your rows are contiguous you can use

myArray = Sheets("Data").Range("A1:Z99").Value

Other than that, AFAIK you would need to use a loop.

Added to which you can only ReDim Preserve the 2nd part of the array.
At the moment you have 1 to 1 1 to 26. If you wanted to make that 1 to 2 1 to 26. you cant use redim preserve

 

[hs2610]

If your rows are contiguous you can use

myArray = Sheets("Data").Range("A1:Z99").Value

Other than that, AFAIK you would need to use a loop.

Added to which you can only ReDim Preserve the 2nd part of the array.
At the moment you have 1 to 1 1 to 26. If you wanted to make that 1 to 2 1 to 26. you cant use redim preserve

I will be capturing the data from same row everytime. Data in Range("A1:Z1") changes contiously. So i need to capture those changes in an array and paste the data in excel periodically.

 

[Fluff]

In that case you would need to loop through the array like

Sub AddtoArray()
Dim Ary As Variant, i As Long
Ary = Application.Transpose(Range("A1:Z1"))
ReDim Preserve Ary(1 To 26, 1 To UBound(Ary, 2) + 1)
For i = 1 To 26
   Ary(i, UBound(Ary, 2)) = Cells(1, i)
Next i
End Sub

 

[Norie]

Where in the array do you want to insert the row?

 

[hs2610]

In that case you would need to loop through the array like

Sub AddtoArray()
Dim Ary As Variant, i As Long
Ary = Application.Transpose(Range("A1:Z1"))
ReDim Preserve Ary(1 To 26, 1 To UBound(Ary, 2) + 1)
For i = 1 To 26
   Ary(i, UBound(Ary, 2)) = Cells(1, i)
Next i
End Sub

This code will capture the data cell by cell, however I want to capture the whole range, i.e. Range("A1:Z1") in one go.

 

[hs2610]

Where in the array do you want to insert the row?

This is a continuous process, so its like -

a, b, c, d, e, f, g
a1, b1, c1, d1, e1, f1, g1
.
.
.
a30, b30, c30, d30, e30, f30, g30

After this, I will paste the data in the excel, clear the array and start updating the values in the array, once again.

 

[Norie]

Do you actually want to append a new row to the array rather than insert a row in the array?

 

[hs2610]

Do you actually want to append a new row to the array rather than insert a row in the array?

Yes

 

[Norie]

If that's the case you would need to use code like that Fluff posted in post #4 .