CODEWITHSUNDEEP
arraysvbaexcel
OfficialBenWhite
OfficialBenWhite

Reputation: 141

Is it possible to truncate a multidimensional array in VBA preserving the data already contained in it?

I have an array that I preallocate a bunch of memory before populating, once populate i would like to remove the empty rows at the end, however I get an error. Any suggestions of a good way to do this, without using a second for loop?

Dim myArray() as Variant
ReDim myArray( 1 to 800, 1 to 50)

For i = 1 to 800
' .....fill the array
Next i

Then the next following funcition call fails

ReDim Preserve myArray(1 to 50, 1 to 50)

with the error message:

"Run- time error '9':

Subscript out of range"

Upvotes: 0

Views: 2208

Answers (2)

SJR
SJR

Reputation: 23081

This is not a proper answer to your question, but with regard to avoiding loops, you can experiment with this approach

Sub Test2()

Dim arrMy()

ReDim arrMy(1 To 5, 1 To 5)
Dim i, j
For i = 1 To 5
    For j = 1 To 5
        arrMy(i, j) = i * j
    Next j
Next i

Range("A1").Resize(5, 5) = arrMy

Dim arrFinal()

ReDim arrFinal(1 To 2, 1 To 5)

arrFinal = Application.Index(arrMy, Evaluate("ROW(1:2)"), Array(1, 2, 3, 4, 5))

Range("H1").Resize(2, 5) = arrFinal

End Sub

Upvotes: 0

rohrl77
rohrl77

Reputation: 3337

You can use Redim Preserve in order to redimension an array. However, this will only work for the last dimension of the array. Here a quick example of doing a redim preserve:

Sub Test()
Dim arrMy()
ReDim arrMy(1 To 10, 1 To 10)
Dim i, j
For i = 1 To 10
    For j = 1 To 10
        arrMy(i, j) = 1
    Next j
Next i

ReDim Preserve arrMy(1 To 10, 1 To 1)

End Sub

In order to sidestep this limitation you can simply create a new array, size it appropriately, and fill it using the data from the first array.

Here is an example of this:

Sub Test2()
Dim arrMy()
ReDim arrMy(1 To 10, 1 To 10)
Dim i, j
For i = 1 To 10
    For j = 1 To 10
        arrMy(i, j) = 1
    Next j
Next i

Dim arrFinal()
ReDim arrFinal(1 To 2, 1 To 10)
For i = 1 To 2
    For j = 1 To 10
        arrFinal(i, j) = arrMy(i, j)
        arrFinal(i, j) = arrMy(i, j)
        arrFinal(i, 2) = arrMy(i, j)
    Next j
Next i

End Sub

Upvotes: 2

Related Questions