Creating array of selected columns from named table

[Westbury]

My fundamental question is how do I put columns 1, 7 & 8 from a named table into an array?

I've studied a similar question in Best way of putting various columns from a Table into an array and particularly the solution identified by Fluff
but keep getting a runtime error 9 subscript out of range.

My named table definitely exists but it appears that the vba doesn't recognise it. I'm using Excel 2013 if that is of significance.

This is the code so far..

Sub ColumnsTable_To_Array()

Dim myTable As ListObject
Dim myArray As Variant

'Set path for Table variable
myArray = Worksheets("Data list").ListObjects("Data").Range

Erase myArray
End Sub

Thanks
Geoff

 

[Westbury]

yes, thanks

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Rick Rothstein]

Similar to the link you referred to, but structured differently...

Sub Westbury()
  Dim Arr As Variant
  With Worksheets("Data list").ListObjects("Data").DataBodyRange
    Arr = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{1,7,8}])
  End With
  '
  '  Arr is an array containing all the rows of data from the referenced
  '  table drawn from the 1st, 7th and 8th columns of that table
  '
End Sub

 

[Westbury]

Thanks Rick,

I've tried your solution and and can confirm that it works fine.

Geoff

 

[Westbury]

It would be useful to be able to copy the first row of the data table with the column identities. Given that this is now a named table is there any special command to do this ?

 

[Rick Rothstein]

Just change the DataBodyRange property of the ListObjects object to Range.

 

[Westbury]

It would be useful to be able to copy the first row of the data table with the column identities. Given that this is now a named table is there any special command to do this ?

but with the same selection of cells, cols 1,7 and 8

 

[Rick Rothstein]

Yes, what I suggested will do that. Here is the code with the modification I mentioned...

Sub Westbury()
  Dim Arr As Variant
  With Worksheets("Data list").ListObjects("Data").Range
    Arr = Application.Index(.Value, Evaluate("ROW(1:" & .Rows.Count & ")"), [{1,7,8}])
  End With
  '
  '  Arr is an array containing all the rows of data from the referenced
  '  table drawn from the 1st, 7th and 8th columns of that table
  '
End Sub

 

[Westbury]

Rick,

thanks for your reply. This last code produces an array of the table data including the headers. I'm now looking for an array just to capture the headers, but agian only columns 1,7,8

You posted VBA - Collecting all the heading names into an array in 2017. The code

Headers = Application.Index(Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Value, 1, 0)[CODE=vba]

gives me all of the headers. How do I convert this to refer to my named table and only to pick up cols 1,7 & 8 ?

Thanks.

 

[Rick Rothstein]

Try it this way...

Sub GetCertainHeaders()
  Arr = Application.Index(Worksheets("Data list").ListObjects("Data").HeaderRowRange,1,[{1,7,8}])
  '
  '  Arr is an array containing the values from Columns 1,7,8 of the table's header row
  '
End Sub