Create multidimensional array from several non adjacent table columns

[ddualba]

Greetings,

Im trying to explore my understanding of arrays and loading them up with excel table data.

I can create a single column array and put a table column in it (as well as display it) like below




Arr1 = Range("myData[Name]")

tblRows = myTable.DataBodyRange.Rows.Count

For i = 1 to tblRows
Debug.Print Arr1(i, 1)
Next i


My problem is when I want to create a multidimensional array and put several columns in them (The columns are non adjacent)



Sub TableColumnsToArray()

Dim Arr1() As Variant 'my array, currently has no size
Dim dataSheet As Worksheet
Dim myTable As ListObject
Dim tblRows As Long
Dim i As Integer

Set dataSheet = ThisWorkbook.Worksheets("Data_Table")
Set myTable = dataSheet.ListObjects("myData")
tblRows = myTable.DataBodyRange.Rows.Count

Arr1 = Range("myData[Name],myData[Attribute5]") ' This line does not receive the debug error, but could be the source of the problem

For i = 1 To tblRows
Debug.Print Arr1(i, 1) & " : "; Arr1(i, 2) ' This row receives a subscript out of range error
Next i





I might perhaps need a ReDim statement to properly size the array for this to work. Not sure whats wrong and whats possible. I am able to use 2 separate arrays, 1 for each column and get the results, but not sure if this is the way it has to be done. I have several more columns from the same table I want to add to the array and I dont want to have an array for each column.

Any help is appreciated. Thanks

[snb]

See: http://www.snb-vba.eu/VBA_Arrays_en.html

[ddualba]

Thanks. Looks like I did a little more research and figured it out.

Need to Redim the array to the appropriate number of columns and then add each table column on a separate row. code below worked for me.




ReDim arr(1 To tblRows, 1 To 3)

For i = 1 To tblRows ' loop through table and add appropriate rows to array
IF someAttrib = "myCondition" Then
arr(r, 1) = myTable.ListColumns("EntityName").DataBodyRange(i)
arr(r, 2) = myTable.ListColumns("AdjName").DataBodyRange(i)
arr(r, 3) = myTable.ListColumns("BegBal").DataBodyRange(i)
End if
Next i

[snb]

simpler: use autofilter/copy or advancedfilter. read the resulting range into the array in 1 go : sn=range('result')