Best way of putting various columns from a Table into an array

[RawlinsCross]

Good day,

I have an excel table with 15 columns and I want to place 7 of these columns (non-contiguous) in a 2-D variant array. What are my best options here? I have a thought of making a named range with the various columns but this seems like I'm ignoring the ListColumns property inherent in tables? Thoughts?

 

[JoeMo]

Say your table is named "Table1". Then you could put the entire table's data into a 2-D variant array and just work with the specific columns of interest. Maybe something like this:

Sub Test()
Dim R As Range, V As Variant, LO As ListObject
Set LO = ActiveSheet.ListObjects("Table1")
Set R = LO.DataBodyRange
V = R.Value
'rest of code
'example of values in first data row of columns 1,3, and 5 of the table
MsgBox V(1, 1) & vbTab & V(1, 3) & vbTab & V(1, 5)
End Sub

 

[RawlinsCross]

Hi JoeMo,

The issue is that I want to put the resulting array in a ListBox on a userform with a ColumnCount of 7. So I want to take from Table1 columns 2, 4, 6, 7, 8, 9, 11 - and put them in the variant array. Then do some adjustments on the array (we don't need to worry about that here) and then put the array into the listbox in its entirety.

 

[Fluff]

How about

Sub RawlinsCross()
   Dim Ary As Variant
  
   Ary = ActiveSheet.ListObjects(1).DataBodyRange.Value2
   Ary = Application.Index(Ary, Evaluate("row(1:" & UBound(Ary) & ")"), Array(2, 4, 6, 7, 8, 9, 11))
End Sub

 

[RawlinsCross]

How interesting is that (and what an honor to have a subroutine named after me! hahaha). The "evaluation" keyword in particular is very neat. Looking up on it now.

I'm thinking I can get two birds stoned at once here! (Canadian TV show joke). I can do the adjustments I hinted in my first message to flag the rows I want to keep and put them in that first argument as another array. Very cool! (I think)

 

[Fluff]

I'm afraid you can't do that.

 

[RawlinsCross]

No? So I can't do something like this?

Ary = Application.Index(Ary, Evaluate(Array(1, 3, 4, 5, 6, 7, 9, 11, 45, 56), Array(2, 4, 6, 7, 8, 9, 11))

The first array would be formed by an evaluation subroutine that would select row numbers?

 

[Fluff]

Did you try?

 

[RawlinsCross]

Point taken! Let me work this tomorrow and I'll post what code ends up working

 

[RawlinsCross]

Well, I was going to put in some subroutine that scanned the array to make another raw of filtered rows and pass that onto the first argument of Evaluate but I thought it too complex relative to the alternative. So the selected columns of the table that form the array go into a userform multi-column listbox so I ended up doing the following:
1. Dump the entire table into the array
2. Select those columns that will be in the listbox (via the Index/Evaluate code Fluff mentioned above)
3. Insert the array into the listbox
4. Run the filter on the listbox entries

Set mwSht = ThisWorkbook.Worksheets("RawData")
vTableArray = mwSht.ListObjects("Table1").DataBodyRange.Value
'Only include the relevant columns
vTableArray = Application.Index(vTableArray, Evaluate("row(1:" & UBound(vTableArray) & ")"), Array(2, 4, 6, 7, 8, 9, 11))

Me.lbHistory.List = vTableArray

Call CleanUpListBox

Clean Up ListBox

Private Sub CleanUpListBox()

Dim i As Long

With Me.lbHistory
For i = .ListCount - 1 To 0 Step -1
     If .List(i, 2) = "-" Then
        .RemoveItem (i)
    Else
        'perform some minor formatting on remaining rows in certain columns
        .List(i, 0) = Format(.List(i, 0), "dd-mmm-yy h:mm AM/PM") 'Format Date
        .List(i, 1) = Format(.List(i, 1), "0.00") 'Format duration in hours
    End If
Next i
End With

End Sub