VBA Create Matrix from Multiple ListBox containing headers

Question

I have a ListBox where are some columns names, and I'd like to build a matrix with only the selected columns in it. Is there a simple way to do that?

I'm actually trying with :

Dim Matrix_a() As Variant
J = 0

For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
       J = J + 1
       Matrix_a.Columns(J) = Price.Columns(i)
    End If
Next i

where "Price" is a previously defined range. this doesn't work because of an "incorrect qualificator", but I don't see how to bypass that (my VBA level being quite low).

Am I missing something?

Answer 1

many issues here:

1. since you have declared Matrix_a as a dynamic array, you need to dimension it before use
2. you dont need the .Columns reference to Matrix_a
3. ListBox proerty is Column, not Columns
4. the ListBox Selected property applies to rows, not columns

Dim'ing an array

'Declaration
Dim Matrix_a() As Variant

' Set a size
Redim Matrix_a(0 to 1, 0 to 1)

' Resize, keeping any existing data intact
Redim Preserve Matrix_a(0 to 1, 0 to 2)

' you can use variables
i = 3
Redim Preserve Matrix_a(0 to 1, 0 to i)

' Note: you can only change the last dimenstion with Preserve
' Having declared (0 to 1, 0 to 1), this won't work:
Redim Preserve Matrix_a(0 to 2, 0 to 1)