Creating array of selected columns from named table

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
125
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..

VBA Code:
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,575
Office Version
  1. 2010
Platform
  1. Windows
Similar to the link you referred to, but structured differently...
VBA Code:
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

Board Regular
Joined
Jun 7, 2009
Messages
125
Thanks Rick,

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

Geoff
 

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
125

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,575
Office Version
  1. 2010
Platform
  1. Windows
Just change the DataBodyRange property of the ListObjects object to Range.
 

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
125

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,575
Office Version
  1. 2010
Platform
  1. Windows
Yes, what I suggested will do that. Here is the code with the modification I mentioned...
VBA Code:
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

Board Regular
Joined
Jun 7, 2009
Messages
125
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
VBA 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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,575
Office Version
  1. 2010
Platform
  1. Windows
Try it this way...
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,118,806
Messages
5,574,424
Members
412,591
Latest member
dawnkotzebue
Top