Dynamic Charting
 

I frequently make simple date & total graphs using rolling 52 week sales
totals for different product lines. The dates are always in the top row
(D1:BC1). The totals are always the last row of those same columns (D though
BC). Unfortunately the last row can change depending upon the number of
products in that line. The recorded macro below works when the totals are on
the 6th row. I've been trying to modify this with some sort of €œlastrow"
function to dynamically change the "D6:BC6" to the appropriate row, but so
far haven't been able to get anything I've tried to work. Please help.

Range("D1:BC1,D6:BC6").Select
Range("BC6").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("week").Range("D1:BC1,D6:BC6")
ActiveChart.Location Whe=xlLocationAsObject, Name:="week"
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnitIsAuto = True
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = True
End With

Dynamic Charting
 

Try this at the start of to select the range

Dim r1, r2, myrange As Range

lrow = Range("D1").End(xlDown).Row
Set r1 = Range(Cells(1, 4), Cells(1, 55))
Set r2 = Range(Cells(lrow, 4), Cells(lrow, 55))
Set myrange = Union(r1, r2)
myrange.Select
Cells(lrow, 55).Activate

The xlDown only works if there are no blank rows until the end.

Then use this for your data source line

ActiveChart.SetSourceData Source:=myrange

stone-man wrote:
I frequently make simple date & total graphs using rolling 52 week sales
totals for different product lines. The dates are always in the top row
(D1:BC1). The totals are always the last row of those same columns (D though
BC). Unfortunately the last row can change depending upon the number of
products in that line. The recorded macro below works when the totals are on
the 6th row. I've been trying to modify this with some sort of "lastrow"
function to dynamically change the "D6:BC6" to the appropriate row, but so
far haven't been able to get anything I've tried to work. Please help.

Range("D1:BC1,D6:BC6").Select
Range("BC6").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("week").Range("D1:BC1,D6:BC6")
ActiveChart.Location Whe=xlLocationAsObject, Name:="week"
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnitIsAuto = True
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = True
End With

Dynamic Charting
 

Thanks. That's just what I needed !

" wrote:

Try this at the start of to select the range

Dim r1, r2, myrange As Range

lrow = Range("D1").End(xlDown).Row
Set r1 = Range(Cells(1, 4), Cells(1, 55))
Set r2 = Range(Cells(lrow, 4), Cells(lrow, 55))
Set myrange = Union(r1, r2)
myrange.Select
Cells(lrow, 55).Activate

The xlDown only works if there are no blank rows until the end.

Then use this for your data source line

ActiveChart.SetSourceData Source:=myrange

stone-man wrote:
I frequently make simple date & total graphs using rolling 52 week sales
totals for different product lines. The dates are always in the top row
(D1:BC1). The totals are always the last row of those same columns (D though
BC). Unfortunately the last row can change depending upon the number of
products in that line. The recorded macro below works when the totals are on
the 6th row. I've been trying to modify this with some sort of "lastrow"
function to dynamically change the "D6:BC6" to the appropriate row, but so
far haven't been able to get anything I've tried to work. Please help.

Range("D1:BC1,D6:BC6").Select
Range("BC6").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("week").Range("D1:BC1,D6:BC6")
ActiveChart.Location Whe=xlLocationAsObject, Name:="week"
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnitIsAuto = True
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = True
End With