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

Question

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.

score 2 · Accepted Answer · answered Aug 01 '17 at 22:57

Try this loop instead of yours:

    For i = 1 To sourceData.ListColumns.count
      With cht.SeriesCollection.NewSeries
        .name = sourceData.ListColumns(i).name
        .values = sourceData.ListColumns(i).DataBodyRange. _
          Offset(3).Resize(sourceData.ListRows.count - 3)
      End With
    Next i

score 1 · Answer 2 · answered Aug 01 '17 at 22:30

You can iterate the ListColumns of the table and use Offset and Resize to get a specific set of rows from the DataBodyRange of each `ListColumn'.

For rows 4 through 10, you need to to offset by 3 rows, and then resize by 7 rows. You can adjust those numbers in the sample code below if the number of interesting rows changes, or if the first row index changes:

Option Explicit

Sub TableSection()

    Dim ws As Worksheet
    Dim lst As ListObject
    Dim lcl As ListColumn
    Dim i As Long
    Dim var As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1") '<-- change to your sheet
    Set lst = ws.ListObjects("Table1") '<-- change to your table

    For Each lcl In lst.ListColumns
        ' change 3 and 7 depending on the rows you are interested in
        var = lcl.DataBodyRange.Offset(3, 0).Resize(7, 1)
    Next lcl

End Sub

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

2 Answers2

Linked