thanks very much for your help

ill give it a try when i get some time.

"OssieMac" wrote:

Hi Joseph,

Your quote "I only want to chart 3 rows the problem is the rows will always
be last 3 rows on the sheet"

Perhaps I interpreted incorrectly. Anyway try the following. It selects the
last 3 entire rows and names them as the range for the chart series. However,
it assumes that the column headers are on the first row of the 3 rows.

Your quote: "sometimes it actually exceeds the max columns (and we need to
do it in 2 or more sections)" I have not made any attempt to handle multiple
charts if the data exceeds specific limits. You will need a method of
counting the rows and columns and then treat each section as a separate
entity. I have included a line of code to show you how to find the last
column and from there you can decide whether to divide the data into several
charts. Having decided on the size of your charts, you can name the first and
last cell of the ranges for chart 1, chart 2 etc.

If you are uncertain of the name of an existing chart to include it in your
code like where I have used Chart 1, you can select any cell in a worksheet
and then turn on the macro recorder and then select the chart and turn the
macro recorder off and you will be able to find the name in the recorded code.

Sub AssignChartRange()

Dim lastRowNumb As Long
Dim firstRowNumb As Long
Dim lastColNumb As Long

'Find the last used row on the worksheet and
'calculate the first row for chart series range.
'If chart is selected then code below fails.
'Therefore select the worksheet first.
Sheets("Calc").Select
With Sheets("Calc")
'Edit "A" to any column of your data where there
'will be data in the last row.
lastRowNumb = .Cells(.Rows.Count, "A").End(xlUp).Row
firstRowNumb = lastRowNumb - 2

'Following is example only for finding last used
'column number on the last used row.
'Not used for anything in the remaining code
lastColNumb = .Cells(lastRowNumb, .Columns.Count) _
.End(xlToLeft).Column
End With

'Name the 3 rows for the chart series range
ActiveWorkbook.Names.Add Name:="ChartSeriesRnge", _
RefersToR1C1:=Sheets("Calc").Range(Cells(firstRowN umb, "A"), _
Cells(lastRowNumb, "A")).EntireRow

With Worksheets("Labour Histogram") _
.ChartObjects("Chart 1").Chart

.SetSourceData Source:=Sheets("Calc") _
.Range("ChartSeriesRnge"), PlotBy:=xlRows

.HasTitle = True
.ChartTitle.Characters.Text = "My Chart Title"
.ChartTitle.Font.Bold = True

'Following added for information.
'Delete if not required
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle. _
Characters.Text = "My X Axis"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle. _
Characters.Text = "My Y Axis"
End With

End Sub


--
Regards,

OssieMac