How to do a dynamic plot in Excel via VBA?

[Wal]

Hi I want to write a code in VBA in which a dynamic set of rows ( the
number of columns is fixed to 4) in excel are to be read and a plot
type: " xlXYScatterSmooth" Could someone help me figure out how to
write this one? thanks

 

[Jon Peltier]

How is the number of rows specified?

- Jon

 

[Wal]

Hi Jon
The number of rows is dynamic. The data will be grabbed from somewhere
else and pasted in the spreadsheet in which the number of column is
alwasy the same but the number of rows will change. So I want a VBA
that can grab the data i paste in excel and produce automatically a
chart.

Thanks

 

[Jon Peltier]

New chart each time, or reusing the same chart?

If the data is properly shaped*, you need only select one cell in the range
and Excel will create a new chart using the whole block of data. For an
existing chart, you could grab this region and apply it to the chart:

ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=ActiveSheet.Range("A1").CurrentRegion

Or you might consider the non-VBA dynamic chart approach:

http://peltiertech.com/Excel/Charts/Dynamics.html

*Properly-shaped data:
- Series in columns
- No empty columns or rows
- Surrounded by empty columns and rows
(or butted against top and left edges of sheet)
- First column = X values (category labels)
- First row = series names
- Top left cell blank

- Jon