Adding rows to a 2 dimensional array ...

  • Thread starter Thread starter netnews.comcast.net
  • Start date Start date
N

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
 
Hi,
Try using the statement ReDim Preserve .. It might solve.. Im not sure bcos
it works in VB dono about VBA
 
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
 
Thanks to all; both solutions give me options and can work for me.

-Ralph Fraley
 
Back
Top