How to set range for charts?

[Guest]

I am creating automatic charts to be updated on a weekly basis by simply
recording the macros while creating them in Excel and then run the macro
every week to update the charts.

I have a range that can be either week 1-52 (full year) or rolling over 52
weeks (from August to August for example).

How do i set the range based on current date i.e. I do not want to show the
remaining weeks of the year empty as it affect trend lines??

 

[Guest]

i.e how can I avoid having a set range below to be from E to BC and instead
be from E to x (something I define)?

Sub Macro1()
'
' Macro1 Macro
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("1st cut from pivot").Range( _
"E13:BC13,E19:BC19,E28:BC28"), PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart1"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Trendlines.Add(Type:=xlLogarithmic,
Forward _
:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Trendlines.Add(Type:=xlLogarithmic,
Forward _
:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:=False).Select
Selection.Delete
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLogarithmic,
Forward _
:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:=False).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Trendlines.Add(Type:=xlLogarithmic,
Forward _
:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:=False).Select
End Sub