Series formula too long error in VBA charts

[adventurer]

Hi,

I have the following data with 23 departments.
The row one department cells are merged.

testdata12.png

I am trying to make two charts using VBA; one for Actual and one For % of total by the following code. I am getting two graphs until in the For loop of i Column the values do not exceed 35 and 36 respectively in place of 29 and 30 respectively. As I exceed the values 35 and 36 resp, it shows 'Series Formula Too Long' error and the grphs are not plotted. Please tell me why this is happening and what is the solution for the same.
Thanks.

Sub TwoCharts11()


Dim rChart1 As Range
Dim rChart2 As Range
Dim iColumn As Long


Dim cht1 As Chart
Dim cht2 As Chart


Const StrtRow As Long = 2
Const EndRow As Long = 6


With ActiveSheet


Set rChart1 = .Range(.Cells(StrtRow, "B"), .Cells(EndRow, "B"))
Set rChart2 = .Range(.Cells(StrtRow, "B"), .Cells(EndRow, "B"))


For iColumn = 3 To 29 Step 2
Set rChart1 = Union(rChart1, .Range(.Cells(StrtRow, iColumn), .Cells(EndRow, iColumn)))
Next


For iColumn = 4 To 30 Step 2
Set rChart2 = Union(rChart2, .Range(.Cells(StrtRow, iColumn), .Cells(EndRow, iColumn)))
Next


Set cht1 = .Shapes.AddChart.Chart
Set cht2 = .Shapes.AddChart.Chart

With cht1
.Parent.Left = .Parent.Left - .Parent.Width / 2
.ChartType = xlColumnClustered
.SetSourceData rChart1
End With

With cht2
.Parent.Left = .Parent.Left + .Parent.Width / 2
.ChartType = xlColumnClustered
.SetSourceData rChart2
End With

End With

End Sub

Kindly find the image at http://peltiertech.com/images/2015-0...tsPartDeux.png My data set has 23 pcts and acts.

I have also posted my question on other forums as follows:http://stackoverflow.com/questions/3...-in-vba-charts

http://www.excelkey.com/forum/viewto...p=22008#p22008

http://www.ozgrid.com/forum/showthre...574#post750574

http://www.mrexcel.com/forum/excel-q...ml#post4203489

http://stackoverflow.com/questions/3...-in-vba-charts