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?

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Peter_SSs]

Unless I have misinterpreted what you are trying to do, you can select the rows & columns to go directly into an array.
If you know the rows and columns ..

Sub RawlinsCross_v2()
   Dim Ary As Variant

   Ary = ActiveSheet.ListObjects(1).DataBodyRange.Value2
   Ary = Application.Index(Ary, Application.Transpose(Array(1, 3, 4, 5, 6, 7, 9, 11, 45, 56)), Array(1, 2, 4, 6, 7, 9, 11))
End Sub

Note also that if you want to, say, reorder the columns into your array, just list them in the order that you want them to appear in the arry:

Sub RawlinsCross_v2a()
   Dim Ary As Variant
 
   Ary = ActiveSheet.ListObjects(1).DataBodyRange.Value2
   Ary = Application.Index(Ary, Application.Transpose(Array(1, 3, 4, 5, 6, 7, 9, 11, 45, 56)), Array(1, 9, 4, 2, 7, 6, 11))
End Sub

If you want to do some evaluation to determine the rows to go in, then that can be done too. For example, for the given table, this should read columns 1, 2, 4, 6, 7, 9, 11 for all rows where column 3 is not equal to "-" into array Ary

Sub RawlinsCross_v3()
  Dim Ary As Variant, aRws As Variant

  With ActiveSheet.ListObjects(1).DataBodyRange
    aRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#=""-"",""x"",Row(#)-%)", "#", .Columns(3).Address), "%", .Rows(0).Row))), "x", False)
    Ary = Application.Index(.Value, Application.Transpose(aRws), Array(1, 2, 4, 6, 7, 9, 11))
  End With
End Sub

 

[RawlinsCross]

Hey Peter,

Yeah, I was hoping to do a filter prior to entering the array into the userform ListBox but I found the syntax a little confusing. So I'm really happy to see your reply - although still confused with that syntax. It's awfully messy.

 

[Peter_SSs]

It's awfully messy.

Well you can still use your looping procedure if you want - nothing wrong with it at all.
I was simply demonstrating a way to ..

flag the rows I want to keep and put them in that first argument as another array.

.. and its all done in a few lines of code.

Yeah, I was hoping to do a filter prior to entering the array

Here is another way, using your idea of filtering the table first, but I don't see any particular advantage in it as there is a fair bit of 'fiddling' & looping.
Again, this is getting the same data as my previous code. That is, filtering on the 3rd column of the table for not "-"

Sub RawlinsCross_v4()
  Dim Ary As Variant
  Dim rA As Range
  Dim s As String
  Dim i As Long, HdrRow As Long

  Application.ScreenUpdating = False
  With ActiveSheet.ListObjects(1)
    HdrRow = .Range.Row
    .AutoFilter.ShowAllData
    .Range.AutoFilter Field:=3, Criteria1:="<>-"
    For Each rA In .DataBodyRange.Columns(1).SpecialCells(xlVisible).Areas
      For i = rA.Row - HdrRow To rA.Row - HdrRow + rA.Rows.Count - 1
        s = s & " " & i
      Next i
    Next rA
    .AutoFilter.ShowAllData
    Ary = Application.Index(.DataBodyRange.Value, Application.Transpose(Split(Mid(s, 2))), Array(1, 2, 4, 6, 7, 9, 11))
  End With
  Application.ScreenUpdating = True
End Sub

 

[RawlinsCross]

Now, when I say messy, it's probably messy only cause I'm unfamiliar with it. So with that having been said, would you mind if I try to walk through this syntax? So we want to take a table and remove the rows that have "-" in column 3. Fair enough! So you have the filter:

aRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#=""-"",""x"",Row(#)-%)", "#", .Columns(3).Address), "%", .Rows(0).Row))), "x", False)

So if I can deconstruct this:
1. Filter - okay, self - explanatory
2. Transpose - the index function takes arguments in row format (horrible term sorry). So you need to transpose what is a column format?
3. Evaluate - this is a keyword that evaluates a string command?
4. Replace/Replace - Think you're doing a first replacement on... hmmm... nope, don't got it.
5. Gobbly-gook
- that string is an if statement clearly. # is the cell value? Why all the double quotes? Not sure of the significance of ""x"" or -% (yeah, I'm just lost). Is there a resource you can suggest that explains the conventions that follow Evaluate?

 

[Peter_SSs]

Is there a resource you can suggest that explains the conventions that follow Evaluate?

I haven't gone looking and I picked up most of what I know about in this forum, so I don't have a particular place to refer you to, sorry.

So if I can deconstruct this:

Start on the inside.
The orange part is basically a worksheet formula. Quotes need to be doubled up because the formula is a string with quotes in it. In the formula, # is a placeholder for the cell address, % is a placeholder for the row number of the table header. They are replaced in the string with those two Replace functions. You don't have to use placeholders and replace but I find it easier to understand the formula part and it often is a shorter way to get to the result. In this case if we didn't use placeholder/replace it could have been written like this. Pretty much the same length but the formula string is more disjointed and harder to 'read' for me.

aRws = Filter(Application.Transpose(Evaluate("if(" & .Columns(3).Address & "=""-"",""x"",Row(" & .Columns(3).Address & ")-" & .Rows(0).Row & ")")), "x", False)

Effectively, the formula is like this one in column E looking at my sample dummy table. the result is that if the third column is "-" then the formula returns "x" otherwise it returns the (relative) row number in the table.

RawlinsCross 1.xlsm
	E	F	G	H	I
4
5		Hdr1	Hdr2	Hdr3	Hdr4
6	1	Data 1	27	73	38
7	x	Data 2	71	-	31
8	x	Data 3	30	-	15
9	4	Data 4	65	84	55
10	5	Data 5	5		90
11	6	Data 6	11	97	14
12	x	Data 7	32	-	3
13	8	Data 8	78	21	44
14
Sheet1

Cell Formulas
Range	Formula
E6:E13	E6	=IF(H6="-","x",ROW()-ROW(E$5))

Those values become an array produced by the Evaluate.

Transpose is required to get that array into a the orientation required to filter it.
{1,"x","x",4,5,6,"x",8}

The Filter function excludes (due to the False argument at the end) any element of the array containing an x so the new resultant array is
aRws = {1,4,5,6,8}
That is, the rows we want out of the table

In the Application.Index line that array has to have its orientation changed back again to work as the 'row element' of the index method.

Hope all that made some sense. Easier to do than to explain - as is often the case.