Excel VBA - Selecting multiple table columns for purposes of chart data

Question

I am writing a macro that creates a simple chart based on data from an excel table. I would like to be able to select the from 5 different columns on my table. Those columns are named as followed:

"Calendar date" <-- I would like this to be my x-axis

"AHT", "Target AHT" <-- These would be my primary y-axis

"Transfer", "Target Transfers" <-- Secondary y-axis

I am unsure how to select multiple table columns for my chart data. I can only get it to work if I select 1 single column, and vba doesn't appear to work the same way as it would for a range obj (i.e., range("A:B, D:D, F:G")).

EDIT: The columns in my table that I need to use are 2 and 9:12

Sub myChart()

Dim myChart As Chart, cht As ChartObject
Dim rngChart As Range, destSht As String

destSht = ActiveSheet.Name
Set myChart = Charts.Add
Set myChart = myChart.Location(where:=xlLocationAsObject, Name:=destSht)

*****My issue is with selecting multiple table columns below*****
myChart.SetSourceData Source:=ActiveSheet.ListObjects("Table1").ListColumns(2), PlotBy:=xlColumns
myChart.ChartType = xlColumnClustered

ActiveSheet.ChartObjects(1).Activate

Set cht = ActiveChart.Parent
Set rngChart = Range("A1100:K1115")

cht.Left = rngChart.Left
cht.Top = rngChart.Top
cht.Width = rngChart.Width
cht.Height = rngChart.Height

Range("A2").Select

End Sub

Jon Peltier · Accepted Answer · 2016-06-08 15:36:42Z

2

Include code like this:

Dim rngData As Range

With ActiveSheet.ListObjects("Table1").DataBodyRange
    Set rngData = Union(.Columns(2), .Columns(9), Columns(10), _
        .Columns(11), .Columns(12))
End With

myChart.SetSourceData Source:=rngData, PlotBy:=xlColumns

answered Jun 8, 2016 at 15:36

Jon Peltier

6,1011 gold badge32 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA - Selecting multiple table columns for purposes of chart data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related