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 Where:=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
|
Similar Threads
