Discontiguous Chart Source

[Ladymuck]

I would like to insert a pie chart where the source data is on the active
worksheet but comes from two discontiguous ranges (eg B2:C10 and B15:C15).

I have two ranges GBData, for the first range, and NonGBData for the second
range.

The following code sets the source data as B2:C15, which is incorrect. How
can I amend it to create the chart with only the data I want?

WShtName = ActiveSheet.Name
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets(WShtName).Range( _
GBData.Address, NonGBData.Address), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=WShtName

Many thanks for any suggestions, happy New Year!

 

[galimi]

Ladymuck,

You could create a string that concatenates those range addresses &
separates them via a comma.

 

[Tom Ogilvy]

Even manually, those ranges don't come out properly, but to get the string
you want to get:


ActiveChart.SetSourceData Source:=Sheets(WShtName).Range( _
GBData.Address & "," & NonGBData.Address)

 

[Peter T]

Try this in a new workbook -

Sub test2()

With Range("B2:C10")
.Name = "GBData"
.Columns(1).Value = "a"
.Columns(2).Value = 1
End With
With Range("B15:C15")
.Name = "NonGBData"
.Columns(1).Value = "B"
.Columns(2).Value = 4
End With

' above for testing, real stuff below

Dim sX As String
Dim rYvals As Range
Dim rTL As Range

Set rYvals = Union(Range("GBData").Columns(2),
Range("NonGBData").Columns(2))

sX = "=(" & Range("GBData").Columns(1).Address(, , xlR1C1, True) & ","
sX = sX & Range("NonGBData").Columns(1).Address(, , xlR1C1, True) & ")"

Set rTL = Range("e2")
With ActiveSheet.ChartObjects.Add(rTL.Left, rTL.Top, 200, 200)
With .Chart
.ChartType = xlPie
.SetSourceData _
Source:=rYvals, _
PlotBy:=xlColumns
.SeriesCollection(1).XValues = sX
' more chart properties
End With
End With

End Sub

I first tried to set the source simpy as
..Source:= Union(Range("GBData")., Range("NonGBData")

However this sets the Y-values as columns 1 of the respective ranges with no
category X labels, btw I assume XValues are in the first column-B of the
respective ranges with the y-values in column C

If you want your chart to be 'dynamic', ie to update with dynamic named
ranges, would need separate named ranges for the X & Y values, and instead
of the addresses syntax along the lines of mybook.xls!myname

Regards,
Peter T

 

[Peter T]

I first tried to set the source simpy as

.Source:= Union(Range("GBData")., Range("NonGBData")

I wasn't thinking, that does work if the top left cell is empty

Sub test3()

With Range("B2:C10")
.Name = "GBData"
.Columns(1).Value = "a"
.Columns(2).Value = 1
.Cells(1, 1) = ""
.Cells(1, 2) = "my Pie"
End With
With Range("B15:C15")
.Name = "NonGBData"
.Columns(1).Value = "B"
.Columns(2).Value = 4
End With

Dim rTL As Range
Set rTL = Range("e2")

With ActiveSheet.ChartObjects.Add(rTL.Left, rTL.Top, 200, 200)
With .Chart
.ChartType = xlPie
.SetSourceData _
Source:=Union(Range("GBData"), Range("NonGBData")), _
PlotBy:=xlColumns
End With
End With

End Sub

Regards,
Peter T

 

[Ladymuck]

Thanks for all your suggestions, I'll give them a try and let you know how I
get on!

 

[Ladymuck]

Thanks Tim, very simple solution and it worked a treat. Can't believe I
missed that.

Cheers!