selecting data, and plotting multiple series

NonExcelGuru

New Member
Joined
Nov 20, 2012
Messages
2
I am writing a macro to create an xy-scatter plot. I have selected the range that I wish to plot. I have several series that I would like to be included in the plot (the specific number of series will vary each time and the number of points). I am having trouble with the activechart.setsourcedata and how to properly set it up, so that the range includes all of the data that I have highlighted. Thanks!

Here is my code:
Sub PlotPureSpectra()
'
' PlotPureSpectra Macro
' Plots Pure Spectra
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A19").Select
ActiveCell.FormulaR1C1 = "0"
Range("B19").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*R3C3+R2C3"
Range("A19:B19").Select
Selection.AutoFill Destination:=Range("A19:B2021")
Range("A19:B2021").Select
Rows("1:1").Select
Selection.Copy
Rows("19:19").Select
Selection.Insert Shift:=xlDown
Range("B19").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Range("!$B$19:$AJ$2022")
ActiveChart.PlotArea.Select
ActiveChart.ChartArea.Select
Application.CutCopyMode = False
ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

NonExcelGuru

New Member
Joined
Nov 20, 2012
Messages
2
Adjusted code to say this:
ActiveChart.SetSourceData Source:=ActiveSheet.Range("B19:AJ2202"), PlotBy:=xlColumns

It is working now. However, I would still like it to only plot the number of series present, and not graph empty series. I would love suggestions!
Thanks!
NXG


 

Watch MrExcel Video

Forum statistics

Threads
1,122,980
Messages
5,599,160
Members
414,295
Latest member
Dolenhil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top