Dynamic Array Problem

[Lochnagar]

Hi, I'm very much a newbie to dynamic arrays so apologies if this seems like a trivial question. I have the following vba code, which reads down a column adding the activecell value to the array...it seems to work okay. The reason why I'm wanting to use dynamic arrays is because the number of cells containing values varies.

Sub dynamarray()
Dim DA() As Variant
Dim i As Integer
i = 1
Do Until IsEmpty(ActiveCell.Offset(1, 0))
ReDim Preserve DA(i)
DA(i) = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
End Sub


However, I tried extending it to read into the array the value of the activecell and the value of the cell immediately next door (i.e. A1 and B1, A2 and B2 etc.) using the following,

Sub dynamarray()
Dim DA() As Variant
Dim i As Integer
Dim j As Integer

i = 1
j = 1

Do Until IsEmpty(ActiveCell.Offset(1, 0))
ReDim Preserve DA(i, j)
DA(i) = ActiveCell.Value
DA(j) = ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select
i = i + 1
j = j + 1
Loop
End Sub

...and it doesn't work. Please help, any suggestions comments would be warmly welcomed.

Thanks in advance,

Lochnagar

 

[Richard Schollar]

Howdy!

You'll love variant array assignment - assume you want everything in col A assigned to an array then:

Dim varArray As Variant
 
'assign values to array (does assume at least A1 and A2 contain values)
varArray = Range("A1",Cells(Rows.Count,"A").End(xlUp)).Value
 
'this has created a 2-D array - 2 to however many rows long by 1 column wide
'so to extract value you can:
 
For i = 1 to Ubound(varArray,2)
  MsgBox varArray(i,1)
Next

Everything in colA & colB:

varArray = Range("A1","B" & Cells(Rows.Count,"A").End(xlUp).Row).Value

 

[RoryA]

If you use the Preserve keyword when redimensioning an array, you can only change the last dimension (in your case the number of columns). You would have to build your array horizontally and adjust the way you work with it accordingly.

 

[Lochnagar]

Awesome, thanks for your help guys, really appreciate it. As you pointed out RichardSchollar, I do now love the Array Variant assignment, I only wish I'd heard/read up about it sooner. Thanks again guys.

Lochnagar