Ask Question Forum:
Model Library:2025-02-08:A.I. model is online auto reply
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by duncanb7
at 2024-08-27 03:54:37
ModifyPoint:500 Replies:5 POST_ID:828526USER_ID:11059
Topic:
Visual Basic Programming;Microsoft Office Suite;Microsoft Excel Spreadsheet Software
I have 55000 data and need to be plotted in one curve within one plot.
But in excel, it report out just show or allow the limit 32000 data in my chart I created.
Now Can I solve it in Excel 2003 by VBA ?
I try this following VBA code before, but it is not working, just show me two sepearte curves only, how to combine two into one curve with
correct X-axis value ? OR I also need to seperate the x -axis data into two series and combine it one
by vba code ?
Please advise
Duncan
Sub Chart()
'Plot Y-axis data from two separate series into one
Dim firstrow as long
Dim lastrow as long
Dim seclastrow as long
firstrow=1
lastrow=20000
seclastrow=55000
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.NewSeries 'need to be delete if create is done
ActiveChart.SeriesCollection(1).Values = "=" & sheetname & "!R" & firstrow & "C8:R" & lastrow & "C8"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=" & sheetname & "!R" & lastrow+1 & "C8:R" & seclastrow & "C8"
End Sub
But in excel, it report out just show or allow the limit 32000 data in my chart I created.
Now Can I solve it in Excel 2003 by VBA ?
I try this following VBA code before, but it is not working, just show me two sepearte curves only, how to combine two into one curve with
correct X-axis value ? OR I also need to seperate the x -axis data into two series and combine it one
by vba code ?
Please advise
Duncan
Sub Chart()
'Plot Y-axis data from two separate series into one
Dim firstrow as long
Dim lastrow as long
Dim seclastrow as long
firstrow=1
lastrow=20000
seclastrow=55000
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.NewSeries 'need to be delete if create is done
ActiveChart.SeriesCollection(1).Values = "=" & sheetname & "!R" & firstrow & "C8:R" & lastrow & "C8"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=" & sheetname & "!R" & lastrow+1 & "C8:R" & seclastrow & "C8"
End Sub
Author: duncanb7 replied at 2024-08-27 10:01:00
Finally, I take teylyn's suggestion, take less data into
chart by half.
maybe take even row data since some adjcent data
is similar.
Thanks for all of you reply
Duncan
chart by half.
maybe take even row data since some adjcent data
is similar.
Thanks for all of you reply
Duncan
Assisted Solution
Expert: byundt replied at 2024-08-27 08:25:31
100 points EXCELLENT
The limit in Excel 2003 is 32,000 points per series, and 256,000 points per chart. Your code was failing because you tried to make the second series with 35,000 points.
I revised your code to avoid that pitfall and to use the same marker color. The code assumes that the X-values are in column A and that all the data is on worksheet Sheet2.
I revised your code to avoid that pitfall and to use the same marker color. The code assumes that the X-values are in column A and that all the data is on worksheet Sheet2.
Sub Chart()'Plot Y-axis data from two separate series into oneDim firstrow As LongDim lastrow As LongDim seclastrow As LongDim sheetname As StringDim ser As SeriesApplication.ScreenUpdating = Falsesheetname = "Sheet2"firstrow = 1lastrow = 25000seclastrow = 55000With ActiveSheet.ChartObjects("Chart 4").Chart If .SeriesCollection.Count < 1 Then .SeriesCollection.NewSeries 'need to be delete if create is done .SeriesCollection(1).XValues = "=" & sheetname & "!R" & firstrow & "C1:R" & lastrow & "C1" .SeriesCollection(1).Values = "=" & sheetname & "!R" & firstrow & "C8:R" & lastrow & "C8" .SeriesCollection(1).MarkerForegroundColorIndex = 4 If .SeriesCollection.Count < 2 Then .SeriesCollection.NewSeries .SeriesCollection(2).XValues = "=" & sheetname & "!R" & (lastrow + 1) & "C1:R" & seclastrow & "C1" .SeriesCollection(2).Values = "=" & sheetname & "!R" & (lastrow + 1) & "C8:R" & seclastrow & "C8" .SeriesCollection(2).MarkerForegroundColorIndex = 4End WithEnd Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:
Brad
Expert: Saqib Husain, Syed replied at 2024-08-27 04:14:59
But then unless this is an xy scatter graph the two series would overlap
Expert: Saqib Husain, Syed replied at 2024-08-27 04:11:47
I see that you do not have sheetname defined. Either define it or replace it with
activesheet.name
activesheet.name
Accepted Solution
Expert: teylyn replied at 2024-08-27 04:10:28
400 points EXCELLENT
Hello,
In Excel 2003, the limit for data points in a 2D chart is 32,000. In Excel 2010, the limit is determined by the available memory.
In any case, a chart with more than 32,000 data points will make it impossible to identify an individual data point. The rendered chart would have to be a mile wide to make individual data points distinguishable. So, what's the point of trying to include more than 32,000 data points, if they will not be distinguishable in the first place?
Have you considered creating an aggregation of the data and not plot EVERY data point, but ever nth point instead (like every 10th value, or every 100th value)?
cheers, teylyn
In Excel 2003, the limit for data points in a 2D chart is 32,000. In Excel 2010, the limit is determined by the available memory.
In any case, a chart with more than 32,000 data points will make it impossible to identify an individual data point. The rendered chart would have to be a mile wide to make individual data points distinguishable. So, what's the point of trying to include more than 32,000 data points, if they will not be distinguishable in the first place?
Have you considered creating an aggregation of the data and not plot EVERY data point, but ever nth point instead (like every 10th value, or every 100th value)?
cheers, teylyn
