Reference only part of an excel table Data Body Range with VBA
Solution 1
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
Solution 2
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
Chet K
Updated on June 05, 2022Comments
-
Chet K almost 2 years
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.