Redim Preserve 2D array

Question

I have some code, which takes values from range and put them in an array. So I have 1,000,000 rows and 4 columns.

x= Range("A1:D1000000").value

ReDim Arr(0 To UBound(x, 1), 0 To 4)

for i = 1 to 10
  for y = 1 to 4
    Arr(i - 1, y-1 ) = x(i, y)
  next y
next i

ReDim Preserve ARR(i)

Listbox1.list = Arr

I have tried

ReDim Preserve ARR(i)
ReDim Preserve ARR(i,4)
ReDim Preserve ARR(1,i)

Without Redim Preserve I get all 1.000.000 result with blanks

How to resize preserve to i size so I get result only 10 result?

There's a few ways you can do this but one problem is that you can only resize the last dimension of a multi-dimensional array. — David Zemens
– David Zemens, Commented May 12, 2014 at 14:29
The correct size should be ReDim Arr(1 To UBound(x, 1), 1 To 4) — John Alexiou
– John Alexiou, Commented May 12, 2014 at 16:53

David Zemens · Accepted Answer · 2014-05-12 17:11:11Z

1

You are going to have to restructure your code a little bit. You can only resize the last dimension of a multidimensional array.

What I would probably do unless there is a compelling reason to do otherwise, is to simply resize the Range object you're working with. This is easier than trying to resize the array.

Sub Test()
Dim x As Variant, ARR As Variant
Dim i As Integer
Dim y As Integer

Dim rng As Range  'Declare a range object we will use later

' Assign to our range object
Set rng = Range("A1:D1000000") 

'Resize your range object, it is easier to do this than to resize an array
Set rng = rng.Resize(10, 4)

'Now, your array x will take on the desired size of the range
'assign the rng.Value to array "x"
x = rng.Value

'And the array ARR will also take on this property based on x.
ReDim ARR(0 To UBound(x, 1), 0 To 4)

For i = 1 To 10
  For y = 1 To 4
    ARR(i - 1, y - 1) = x(i, y)
  Next y
Next i

End Sub

UPDATE

If you need to maintain the dimensions of your Range object, then you could do this instead, which will make x an appropriately sized array, without resizing the range object itself.

' Assign to our range object
    Set rng = Range("A1:D1000000") 

'Use the resize method when assigning to array "x":
x = rng.Resize(10, 4).Value

edited May 12, 2014 at 17:11

answered May 12, 2014 at 14:32

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3627579 Over a year ago

Thanks for answer but i need resize dinamicly, so "for i = 1 to DynamicValue"

David Zemens Over a year ago

OK well that was not really clear from your question, in that case, the other accepted answer will be what I meant by "restructuring your code". Cheers.

Ch32k0 · Accepted Answer · 2016-06-16 15:57:49Z

0

x= Range("A1:D1000000").value

ReDim Arr( 0 To 4 , 0 To UBound(x, 1))

for i = 1 to 10
  for y = 1 to 4
    Arr( y-1, i - 1 ) = x(i, y)
  next y
next i

ReDim Preserve ARR(0 to 4, 0 to i)

and instead of:

Listbox1.List = Arr

you should use:

Listbox1.column = Arr

edited Jun 16, 2016 at 15:57

Ch32k0

2443 silver badges18 bronze badges

answered May 13, 2014 at 5:48

user3588043

1361 gold badge4 silver badges16 bronze badges

Comments

Dick Kusleika · Accepted Answer · 2014-05-12 18:50:41Z

Don't read in all million rows. Find the last row of your range that contains data and only bring that data in.

Sub FillLb()

    Dim vaRange As Variant
    Dim aList() As Variant
    Dim i As Long, j As Long
    Dim rLastCell As Range

    Sheet1.ListBox1.Clear

    'Find the last cell that has something ("*") in it by searching
    'backward from A1 using xlPrevious
    Set rLastCell = Sheet1.Range("A1:D1000000").Find("*", Sheet1.Range("A1"), xlValues, , , xlPrevious)

    'Only read in up to the last value
    vaRange = Sheet1.Range("A1", rLastCell).Value
    ReDim aList(0 To UBound(vaRange, 1) - 1, 0 To UBound(vaRange, 2) - 1)
    Sheet1.ListBox1.ColumnCount = UBound(vaRange, 2)

    For i = LBound(vaRange, 1) To UBound(vaRange, 1)
        For j = LBound(vaRange, 2) To UBound(vaRange, 2)
            aList(i - 1, j - 1) = vaRange(i, j)
        Next j
    Next i

    Sheet1.ListBox1.List = aList

End Sub

Collectives™ on Stack Overflow

Redim Preserve 2D array

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related