Trying to graph > 32,000 points

[Ironfist]

All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I'm sure this question has been asked a lot. I searched and couldn't find anything specific to my problems. First things first, I suppose: 1. Windows Vista Enterprise with SP2, 2. MS Office Excel 2007.<o></o>
<o></o>
I have a worksheet that may contain anywhere from 1 to 1,000,000 rows and 3 columns of data which needs to be plotted. Because Excel only allows up to 32,000 data points per series, I am splitting the data up into 30,000 data point sections and plotting each section as its own series. When I have 32,000 data points (on the sheet since the series are fixed at 30,000) or less, everything works perfectly. When I have more points, I get a message which states; “The maximum number of data points you can use in a data series for a 2-D chart is 32,000. If you want to use more than 32,000 data points, you must create two or more series.” What is interesting to me is that the error occurs when I create the chart object on the worksheet, but haven't even added a series yet.<o></o>
<o></o>
Another item of interest is that, when I run the code the first time, no plot appears, however, if I run it again, the second plot appears. Then, when I delete the second plot, the first all of a sudden appears.<o></o>
<o></o>
Can anyone help me trying to figure out these oddities? Thank you for your efforts.

Matthew Lawrence<o></o>
<o></o>

Sub Graph()
   Dim Range1
   Dim Range2
   Dim Range3
   Dim LastRow As Long
   Dim NumberOfRanges As Double
   Dim Chart As ChartObject
   Dim BegNumber As Long
   Dim i As Integer
 
   'Add chart onto active sheet
   Set Chart = ActiveSheet.ChartObjects.Add _
       (Left:=250, Width:=450, Top:=25, Height:=325)
 
   'Set chart parameters
   With Chart.Chart
       .ChartType = xlXYScatterLinesNoMarkers
       .Legend.Delete
   End With
 
   'Find the last row of data on the worksheet
   LastRow = Worksheets("Sheet1").Range("A1048576").End(xlUp).Row
 
   'find how many times to loop
   NumberOfRanges = Application.WorksheetFunction.Ceiling((LastRow - 1) / 30000, 1)
 
   'Start at row two
   BegNumber = 2
 
   'Loop until all all data is graphed
   For i = 0 To NumberOfRanges - 1
       Range1 = Range("G" & BegNumber & ":G" & BegNumber + 30000)
       Range2 = Range("F" & BegNumber & ":F" & BegNumber + 30000)
       Range3 = Range("K" & BegNumber & ":K" & BegNumber + 30000)
 
       With Chart.Chart.SeriesCollection.NewSeries
           .Values = Range2
           .XValues = Range1
           .Border.Color = RGB(255, 0, 0)
       End With
 
       With Chart.Chart.SeriesCollection.NewSeries
           .Values = Range3
           .XValues = Range1
           .Border.Color = RGB(0, 0, 255)
       End With
 
       BegNumber = BegNumber + 30001
 
   Next i
 
End Sub

 

[Ironfist]

A quick update:
The error occurs at the code below. At this point, the ChartObject shouldn’t have any idea how much data is going to get pushed into it, yet it still creates a message if I have over 32,000 data points on the worksheet.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

   'Add chart onto active sheet
   Set Chart = ActiveSheet.ChartObjects.Add _
       (Left:=250, Width:=450, Top:=25, Height:=325)

<o></o>
Also, as a test, I removed the loop in the code and attempted to plot only one series of 30,000 points while still leaving greater than 32,000 points on the worksheet and I still got the message listed in the first post.
<o></o>
Can anyone help?

 

[Ironfist]

Bump up for some help.

 

[Andy Pope]

If the activecell is within your data area excel will complain, even though the chartobject .add methough does not populate the chart.

 

[Ironfist]

Andy,
A super simple answer and it worked perfectly! Thank you so much for your help; it is greatly appreciated!

Matt