parsing blocks of cells for charts

[simon.whitfield]

day 2 of VBA...

I've managed to create a worksheet ("Summary") containing blocks of
cells separated by empty rows. Now I'm struggling with the next bit.

In the first block, cell A1 contains the title of a chart, B1:Q5
contains the chart data. The other blocks are different sizes but with
the same basic layout.

I've also have a subroutine DoChrt for drawing a chart (see below).
What I would like to do now is to have a subroutine that takes each
block of data, identifies the chart title (always top left in the
block) and source data (variable size but same position relative to
title), makes the chart, then moves on to the next block.

I'd be very grateful if anyone has any ideas how to set this up.

Thanks
Simon


Sub DoChrt()
Dim Cht As Chart
Set Cht = Charts.Add
Cht.SetSourceData Source:=Sheets("Summary").Range("B1:Q5")
With Cht
.HasTitle = True
.ChartTitle.Characters.Text = Sheets("Summary").Range("A1")
End With
End Sub

 

[Tom Ogilvy]

Sub DoChrt()
Dim Cht As Chart
Dim rng As Range, ar As Range
Dim Data As Range, titlecell As Range
With Worksheets("Summary")
Set rng = .Columns(1).SpecialCells(xlConstants)
End With
For Each ar In rng.Areas
Set titlecell = ar(1)
Set Data = rng.Parent.Range(titlecell(2, 1), _
titlecell(2, 1).End(xlToRight).End(xlDown))
Set Cht = Charts.Add
Cht.SetSourceData Source:=Data
With Cht
.HasTitle = True
.ChartTitle.Characters.Text = titlecell
End With
Next
End Sub

 

[simon.whitfield]

perfect - thanks very much