Variable Range to Create Chart From
 

I've been working on creating Macros to create charts based on a variable set
of data. The data is entered on a monthly basis and new charts are created
based on the new range. In the Macro that I've pasted the beginning of below
the first thing that I am doing is selecting the greatest possible range then
minimizing that to just the cells with data in them. Those are the values I
am plotting. The problem I am having is selecting the x axis labels that
correspond to the actual data that is being charted. This code works fine as
long as the data starts in the first column possible but it won't happen that
way. This is the first macro that I've attempted so please make it as simple
as possible.

TIA

Sub CompeltedAccurateWP()
'
' CompeltedAccurateWP Macro
'

'
Range("E11:AN13").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Range1 = Selection.Address
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Data Input
sheet").Range(Range1) _
, PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "='Data Input
sheet'!R10C5:R10C40"
ActiveChart.SeriesCollection(1).Name = "='Data Input sheet'!R11C2"
ActiveChart.SeriesCollection(2).XValues = "='Data Input
sheet'!R10C5:R10C40"
ActiveChart.SeriesCollection(2).Name = "='Data Input sheet'!R12C2"
ActiveChart.SeriesCollection(3).XValues = "='Data Input
sheet'!R10C5:R10C40"
ActiveChart.SeriesCollection(3).Name = "='Data Input sheet'!R13C2"
ActiveChart.Location Whe=xlLocationAsNewSheet, Name:= _
"CompletedAccurateWP"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Complete and Accurate Work Packages "
& Chr(1) & "As Rated By Project Team (Scale 1-10)"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = False
' .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Scale (1-10)"
End With