How to Refer to Contiguous ListColumn Ranges by Index

[takoyaki]

Hey all,

I'm writing a macro to set the source data of a chart to several contiguous table columns but I'm stumped at the syntax. The table column headers can change so I'm trying to refer to their index numbers instead.

1 Dim Table1 as ListObject: Set Table1 = Sheet1.ListObjects(1)
2 Dim Chart1 as ChartObject: Set Chart1 = Sheet1.ChartObjects(1)
3 Dim Range1 as Range: Set Range1 = Table1.ListColumns("Column1").Range 'This column will always be named Column1
4 Dim ColumnIndex1 as Long: ColumnIndex1 = 51 'Code within macro determines actual value; subject to change
5 Dim ColumnIndex2 as Long: ColumnIndex2 = 84 'Code within macro determines actual value; subject to change
6
7 Dim Range2 as Range: Set Range2 = Sheet1.Range(Table1.ListColumns(ColumnIndex1).Range, Table1.ListColumns(ColumnIndex2).Range)
8
9 Chart1.Chart.SetSourceData Source:=Union(Range1, Range2), PlotBy:=xlColumns

Line 7 is driving an error: "Run-time error '9': Subscript out of range"

Would anyone happen to know the correct syntax for the bolded line, or have a better method of accomplishing this task?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Syntax: Refer to Contiguous ListColumn Ranges by Index
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[takoyaki]

Understood, this is a cross post from this forum. Apologies for any trouble.