Setting Excel Chart data range with VBA macro

Question

I'm trying to set the data set for a chart object using a macro. The macro selects the correct range that I want (I check by using rng.Select and debugging) but when I right click the chart object after the macro has finished running and select Select Data it says the data range is too complex to display and does not correctly populate my chart. My macro is below. Any pointers appreciated.

EDIT. I have a copy of the sheet where the graph was created from the populated data manually and clicking Select Data on this chart shows the non-contiguous range just fine - it's only when trying to set it by macro for a pre-existing chart that it doesn't work.

Sub test()

UpdateChart 27, 64

End Sub

Sub UpdateChart(ByVal row As Long, ByVal col As Long)

Dim sht As Worksheet
Set sht = Worksheets("Report4_Chart")
Dim data As Worksheet
Set data = Worksheets("Report4")
Dim rng As Range
Dim exclude As Range

data.Activate

Set exclude = data.Rows(25)
Set rng = data.Range("A24", Intersect(data.Rows(row), data.Columns(col)))
Set rng = SetDifference(rng, exclude)
rng.Select

sht.Activate

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=rng

End Sub

enter image description here

How about a data sample? Is the populated "rng" a contiguos range? — teylyn
– teylyn, Commented Jun 14, 2013 at 10:07
So I've added a picture showing a typical data sample with the range I want my graph to contain highlighted in blue. As you can see the range is not contiguous. — baarkerlounger
– baarkerlounger, Commented Jun 14, 2013 at 10:17

Shawn V. Wilson · Accepted Answer · 2016-03-29 18:46:06Z

0

For anyone else who's stumbled across this old question: I just discovered that basing a chart on non-contiguous ranges seems to work only if the ranges form a rectangle.

For instance, I was trying to group cells A1, A7, and C1, and couldn't get it to work. However, when I used A1, C1, A7, and C7, it did work.

(Actually, my ranges were all larger than a single cell. But the "missing" range was the same width as the one above it, and the same height as the one to the side of it. So the combined ranges formed a rectangle.)

edited Mar 29, 2016 at 18:46

answered Mar 29, 2016 at 18:27

Shawn V. Wilson

1,1114 gold badges21 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

Setting Excel Chart data range with VBA macro

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