Reference only part of an excel table Data Body Range with VBA

Viewed 10433

I have found solutions for referencing an entire row/column or a single cell within a table (e.g. The VBA Guide To ListObject Excel Tables), but not a specific range.

Example: The DataBodyRange of Table1 is 3 columns and 10 rows, and I need to plot the data from rows 4-10 in each column (programatically, because the table is dynamic and I want to be able to re-draw the plot if columns are added/removed).

I would expect the syntax to be something like

Sub reDrawChart()

Set sourcedata = ActiveSheet.ListObjects("Table1")
Set cht = Worksheets("Sheet1").ChartObjects(1).Chart

'Delete existing series
cht.ChartArea.ClearContents 

'Add new series
With cht.SeriesCollection.NewSeries
    For i = 1 To nColumns
        .Name = sourcedata.HeaderRowRange(i)
        .Values = sourcedata.DataBodyRange(4:10, i) '<-- Touble spot
    Next i
End With

End Sub

But this is does not work. Are there ways to do what I'm trying to do with this one line? Should I work around it and pursue a different solution?

I would also like to avoid using .Select because on Mac OS it feels like any code that makes VBA stutter has a 50/50 chance to crash to desktop.

2 Answers
Related