Adding rows to a 2 dimensional array ...

[netnews.comcast.net]

Is it possible to add rows to a two dimensional array? I have a situation
where the number of rows can be different each time the array is used. I
need to preserve the existing data in the array when adding the additional
rows. I am using Excel 2003. Any help will be very much appreciated.

Thanks in advance,
Ralph Fraley

 

[Guest]

Hi,
Try using the statement ReDim Preserve .. It might solve.. Im not sure bcos
it works in VB dono about VBA

 

[Kai Uwe Schmidt]

Hi Ralph,

you can use a dynamic two-dimensional aray, which means to create an array
with a not-fixed number of items.
You have to attach the row index to the array´s last dimension, since you
only can dynamically extend the last dimension. After the row size has has
increased, use "Redim Preserve". Perhaps the following might help you.

Best Regards,
Kai

Option Base 1
Dim arMatrix () as String
Dim objWb as workbook
Dim objWs as worksheet
Dim rngMatrix as range
Dim i as integer, j as integer

Set objWb = ActiveWorkbook
Set objWs = objWb.Worksheets(1)
Set rngMatrix = objWs.usedrange
...
For i= 1 to objMatrix.rows.count
For j = 1 to objMatrix.columns.count
arMatrix(j,i) = ws.cells(i,j) 'The dimension whose number
of items is changed must be put at last position.
Next j
Next i
...
'Number of rows has increased by 1.
'Extend the array´s last dimension.

Redim Preserve arMatrix(Ubound(arMatrix(),1),UBound(arMatrix(),2 +1)

'Extend the Source Matrix.
Set rngMatrix = objWs.usedrange

'Add the new line.
With rngMatrix
For j =1 to .columns.count
arMatrix(j,UBound(arMatrix(),2) = .cells(.rows.count,j)
End With
...
End Sub

 

[RB Smissaert]

Download Alan Beban's array functions from this website:
http://home.pacbell.net/beban/
One of the functions is ArrayAdd and I think will do exactly what you want.

RBS

 

[Alan Beban]

Download Alan Beban's array functions from this website:
http://home.pacbell.net/beban/
One of the functions is ArrayAdd and I think will do exactly what you want.

RBS

Hi RB,
The function is ResizeArray.

Alan Beban

 

[netnews.comcast.net]

Thanks to all; both solutions give me options and can work for me.

-Ralph Fraley