You don't need VBA at all, just a dynamic range definition in the worksheet.
This is similar to the "last 12 months" example:

Overview of dynamic charts:
http://peltiertech.com/Excel/Charts/Dynamics.html

Specific example:
http://peltiertech.com/Excel/Charts/DynamicLast12.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Joseph Atie" wrote in message
...
Need a little help, ive got the chart part down, the problem im having is
trying to define the range

the function below is passed an integer. the rows i need to use will be 5
rows below the "row" value. if i use rows(58:60) vba is happy. problem is
the
last row is variable and the values will be 5 rows below the last row. how
can i express this???

this is the function im having trouble with

also im having trouble getting the title to work so any help with that
would
also be appreciated

Sub chart_build(row As Integer)

Sheets("Labour Histogram").Select
ActiveChart.SetSourceData Source:=Sheets("Calc").Rows((row+5):(row+7)),
PlotBy:= _
xlRows

ActiveChart.ChartTitle.text = Sheets("interface").Range("B5")

End Sub

note this is the functions thta builds the chart data incase you wanna
know
where im coming from

Sub total(column As Integer, row As Integer)
Dim counterr As Integer
Dim counterc As Integer
Dim total As Integer

For counterc = 1 To (column * 2)
total = 0
For counterr = 1 To row
total = total + Cells(counterr + 2, counterc + 1).Value
If counterr = row Then
Cells(counterr + 3, counterc + 1).Value = total
End If
Next counterr
Next counterc
counterr = 1
counterc = 1
For counterc = 1 To (column * 2)
If counterc = 1 Then
Cells(row + 6, 1).Value = "Day Shift"
Cells(row + 7, 1).Value = "Night Shift"
End If
'date
Cells(row + 5, counterr + 1).Value = Cells(1, counterc +
1).Value
'day shift
Cells(row + 6, counterr + 1).Value = Cells(row + 3, counterc +
1).Value

counterc = counterc + 1
'night shift
Cells(row + 7, counterr + 1).Value = Cells(row + 3, counterc +
1).Value
counterr = counterr + 1
Next counterc
End Sub