HOW TO REDEFINE AN ARRAY

[sadath]

i have an array name myarray(20,2), populated with data
I want to add one more record at the end, so I done the following

Redim myarray(21,2)

Myarray(21,0)= “abc”
Myarray(21,1)= “def”
Myarray(21,2)= “ghi”

By doing this, data from 0 to 20 th is loosing

Anyone know how tp redefine an array

 

[Richard Schollar]

Hi

You can't expand the size of that dimension and retain the data in it since it isn't the last dimension eg the Preserve keyword would work for the first example in the following, but not the second:

Dim myArray()
Redim myArray(0 to 100, 1 to 5)
'fill with data
'then expand size of last dimension:
'******WORKS!!!!*******
Redim Preserve myArray(0 to 100, 1 to 10)

'not allowed to use Preserve and resize the first dimension:
'so the following fails:
'********ERROR!!!!********
Redim Preserve myArray(0 to 101, 1 to 5)

 

[tusharm]

As Richard has pointed out, only the last dimension can be resized with the Preserve option. But, the first dimension of a 2D matrix can be resized w/o loss of data. You just have to make it the last dimension -- temporarily.

If your matrices are 1-based, you can leverage Excel's TRANSPOSE function as in

Option Explicit
Option Base 1
Sub resizeIt()
    Dim Arr()
    ReDim Arr(19, 9)
    Arr(19, 1) = "19,1"
    Arr = Application.WorksheetFunction.Transpose(Arr)
    ReDim Preserve Arr(UBound(Arr), 25)
    Arr = Application.WorksheetFunction.Transpose(Arr)
    Arr(24, 9) = "24,9"
    Debug.Print Arr(19, 1), Arr(24, 9)
    End Sub

If you want to keep your arrays/matrices zero-based, write your own Transpose function. Or your own ReDim function. Either should be straightforward.

i have an array name myarray(20,2), populated with data
I want to add one more record at the end, so I done the following

Redim myarray(21,2)

Myarray(21,0)= “abc”
Myarray(21,1)= “def”
Myarray(21,2)= “ghi”

By doing this, data from 0 to 20 th is loosing

Anyone know how tp redefine an array

 

[sadath]

Dear Richard,
it doesn't work, may be my codes are wrong

ReDim myarray(0 To 100, 0 To 4)
n = 0
For Each cl In Sheets(plrno).Range("A6:A" & lrow)
myarray(n, 0) = Sheets(plrno).Range("A" & cl.Row)
myarray(n, 1) = Sheets(plrno).Range("b" & cl.Row)
myarray(n, 2) = Sheets(plrno).Range("c" & cl.Row)
myarray(n, 3) = Sheets(plrno).Range("d" & cl.Row)
myarray(n, 4) = Sheets(plrno).Range("e" & cl.Row)
n = n + 1

Next cl
ReDim Preserve myarray(0 To n, 0 To 4)
ListBox1.List = myarray

where I gone wrong?

 

[Richard Schollar]

If your matrices are 1-based, you can leverage Excel's TRANSPOSE function as in

Option Explicit
Option Base 1
Sub resizeIt()
    Dim Arr()
    ReDim Arr(19, 9)
    Arr(19, 1) = "19,1"
    Arr = Application.WorksheetFunction.Transpose(Arr)
    ReDim Preserve Arr(UBound(Arr), 25)
    Arr = Application.WorksheetFunction.Transpose(Arr)
    Arr(24, 9) = "24,9"
    Debug.Print Arr(19, 1), Arr(24, 9)
    End Sub

Hello Tushar

I never knew you could do that! Transpose sure does have its uses.

Another day gone, another Excel lesson learned

Richard

 

[sadath]

Any one knows the answer