HOW TO REDEFINE AN ARRAY

sadath

Board Regular
Joined
Oct 10, 2004
Messages
246
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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
Code:
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

Board Regular
Joined
Oct 10, 2004
Messages
246
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
If your matrices are 1-based, you can leverage Excel's TRANSPOSE function as in
Code:
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 :biggrin:

Richard
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top