Automatic Macro to create 200 graphs

[JoeCL]

Hi Everyone,

I'm struggling with a macro to automatically create about 200 line graphs.

There are a variable number of datapoints in each graph. The data are all in
one column separated by blank lines, like:

123
456
789
887
762

1
2
3

55
44
88
447

I'm new to macros and have tried recording a macro to see the code. The
macro is fine for creating a single graph, but I don't know how to get it to
automatically repeat this to create all the graphs, especially when there
are a variable number of datapoints from one batch to the next of this
spreadsheet.

Can anyone suggest a solution?

I would very much appreciate your help.

Kindest regards,

Julian

 

[Spiggy Topes]

Hi Everyone,



I'm struggling with a macro to automatically create about 200 line graphs..



There are a variable number of datapoints in each graph. The data are allin

one column separated by blank lines, like:



123

456

789

887

762



1

2

3



55

44

88

447



I'm new to macros and have tried recording a macro to see the code. The

macro is fine for creating a single graph, but I don't know how to get itto

automatically repeat this to create all the graphs, especially when there

are a variable number of datapoints from one batch to the next of this

spreadsheet.



Can anyone suggest a solution?



I would very much appreciate your help.



Kindest regards,



Julian

Try something like this. It identifies blank lines and charts all lines since the previous blank line. It stacks generated charts at reasonable intervals so they're not all on top of each other. It's not bullet proof, but should give you enough to expand on.

Option Explicit

Sub Chart_Um()

Dim i As Integer
Dim iRangeStart As Integer
Dim iChartNum As Integer

iChartNum = 0
iRangeStart = 1

For i = 1 To ActiveSheet.UsedRange.Rows.Count + 1

If Cells(i, 1) = "" Then
If i > iRangeStart Then
Range(Cells(iRangeStart, 1), Cells(i - 1, 1)).Select
ActiveSheet.Shapes.AddChart.Select
With ActiveChart
.ChartType = xlColumnClustered
.ChartArea.Top = 20 + (iChartNum * 220)
.ChartArea.Height = 200
iChartNum = iChartNum + 1
End With
End If
iRangeStart = i
End If
Next i

End Sub