# Thread: Create array from array - redim preserve

1. Hi,

Got a dynamic array (arr1) that changes in size (currently 2182 rows x 17 columns)

Looping through the arr1 I'm searching for a user provided variable in a specific column (say column 2). The value is a given criteria (eg. "company name") If the loop encounters a company that meets the criteria, it adds the value in each column to another array (arr2).

In this way, once I'm through arr1, I have in arr2 a subset of arr1 representing only the values I need to work with.

My problem is to fill arr2. Redim preserve will not do it for me as it only allows adjustment of the last dimension of the array.

Below is my current code:

Dim varGetArrayTransactions as Variant

Public Sub Test_TransStep()
Dim arrTransList()
Dim sCoName As String
Dim c, d, e

e = 1

'For test purposes - set Company name
sCoName = "MyCompany"

'Returns range as string and has entire array loaded in varGetArrayTransactions

'Loop entire array
For c = 1 To UBound(varGetArrayTransactions, 1)

'Can we find a matching company?
If sCoName = varGetArrayTransactions(c, 2) Then

'Match found - add values from each column to new array (arrTransList)
For d = 1 To UBound(varGetArrayTransactions, 2)
'This bit doesn't work...!!¤#¤!"#!#!"
ReDim Preserve arrTransList(e, UBound(varGetArrayTransactions, 2))
arrTransList(e, d) = varGetArrayTransactions(c, d)
Debug.Print "Item (" & e & "," & d & "): " & arrTransList(e, d)
Next d

e = e + 1

End If

Next c

TIA

2. Make arr2 a 1D array of arrays instead of a 2D array, or switch the rows and columns over and then transpose it at the end.

3. Hi Rory,

Not sure I understand what you mean by "Make arr2 a 1D array of arrays" - got an example?

I tried the transpose bit for starters, but I find it to be a bit messy to work with, when I have many "rows".

4. In what way is transpose messy?

Can't be specific since you only posted a fraction of your code, but something along the lines of:
Code:
```ReDim Preserve arrTransList(e)
arrTransList(e) = varGetArrayTransactions(c)```
You would of course have to rewrite your called function to return the whole array, not one value at a time.

5. Hi Rory,

Got you on that - thanks for the tip.

I have decided to take a slightly different approach with a fast double iteration, in which I first find the exact number of matches (the UBOUND - then I Redim in accordance with that before entering the loops.

Code looks like this now

e = 1
eMax = 0

For c = 1 To UBound(varGetArrayTransactions, 1)
If sCoName = varGetArrayTransactions(c, 2) Then eMax = eMax + 1
Next c
ReDim arrTransList(eMax, UBound(varGetArrayTransactions, 2))

For c = 1 To UBound(varGetArrayTransactions, 1)
If sCoName = varGetArrayTransactions(c, 2) Then
For d = 1 To UBound(varGetArrayTransactions, 2)
arrTransList(e, d) = varGetArrayTransactions(c, d)
Debug.Print "Item (" & e & "," & d & "): " & arrTransList(e, d)
Next d
e = e + 1
End If
Next c