title: Fixing the date axis of a time series in vba
body: <p>I have a code that gets data from excel and makes a graph. It takes some inputs (since more than one graph is done), creates a new Chart Sheet and insert the data.</p>
  
  <p><strong>Problem 1</strong> My code gets the UsedRange from the specified sheet and uses that to fill the graph. The data starts at A1 and goes until the end. One of the Graphs however, have data in its first row that should not go into the graph. Foe example:</p>
  
  <p>This is for one of the normal graphs:</p>
  
  <pre><code>           Item 1         Item 2
   day 1      100            100
   day 2      110            180
   day 3       90            110
   day 4       70            130
  </code></pre>
  
  <p>and so on. This is the exception:</p>
  
  <pre><code>           Item 1         Item 2
   day 1        1              1
   day 2      110            180
   day 3       90            110
   day 4       70            130
  </code></pre>
  
  <p><strong>QUESTION 1</strong> In this second case, I want to remove the first row of data (not the identifier row) from the graph. Since I am using UsedRage, how can that be done?</p>
  
  <p><strong>Problem 2</strong> For all the graphs, the data series start at some point in the past and comes until today. Problem is that when the date series is graphed, it starts counting from the first day (stepping through the periods, eg. 6 months) until it reaches the most recent date.</p>
  
  <p>In this case, it does not include today's date, which is what I am trying to achieve.</p>
  
  <p><strong>To exemplify:</strong><a href="https://i.stack.imgur.com/uJUXB.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uJUXB.jpg" alt="GraphExample"></a></p>
  
  <p>In this case, my data goes until the 7th of December, but the last date that appears is the 31st of October.</p>
  
  <p><strong>QUESTION 2</strong> Can this be fixed?</p>
  
  <p>Obs: I already tried using the recorder to generate a code for this, but even in the embedded excel options I cannot find any way to do it.</p>
  
  <p><strong>The code so far:</strong></p>
  
  <pre><code>Function Grapher(ChartSheetName As String, SourceWorksheet As String, ChartTitle As String, secAxisTitle As String)
  
  Dim lColumn As Long, lRow As Long
  Dim LastColumn As Long, LastRow As Long
  Dim RetChart As Chart
  Dim w As Workbook
  Dim RetRange As Range
  Dim chrt As Chart
  Dim p As Integer
  Dim x As Long, y As Long
  Dim numMonth As Long
  Dim d1 As Date, d2 As Date
  
  Set w = ThisWorkbook
  
  'find limit
  LastColumn = w.Sheets(SourceWorksheet).Cells(1,   w.Sheets(SourceWorksheet).Columns.Count).End(xlToLeft).column
  LastRow = w.Sheets(SourceWorksheet).Cells(w.Sheets(SourceWorksheet).Rows.Count, "A").End(xlUp).Row
  
  If SourceWorksheet &lt;&gt; "DD" Then 'this is the exception case    
      Set RetRange = w.Sheets(SourceWorksheet).UsedRange 'HOW CAN i CHANGE THE RANGE TO ACCOUNT FOR THE PROBLEM 1?     
  Else    
      Set RetRange = w.Sheets(SourceWorksheet).UsedRange                    
  End If
  
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  For Each chrt In w.Charts
      If chrt.Name = ChartSheetName Then
          Set RetChart = chrt
          RetChart.Activate
          p = 1
      End If
  Next chrt
  If p &lt;&gt; 1 Then
      Set RetChart = Charts.Add
  End If
  'count the number of months in the time series, do the ratio
  d1 = w.Sheets(SourceWorksheet).Range("A2").Value
  d2 = w.Sheets(SourceWorksheet).Range("A" &amp; LastRow).Value    
  numMonth = TestDates(d1, d2)                
  x = Round((numMonth / 15), 1)        
  'ratio to account for period size
  If x &lt; 3 Then
      y = 1
  ElseIf x &gt;= 3 And x &lt; 7 Then
      y = 4
  ElseIf x &gt; 7 Then
      y = 6
  End If            
  'create chart
          With RetChart
              .Select
              .ChartType = xlLine
              .HasTitle = True
              .ChartTitle.Text = ChartTitle
              .SetSourceData Source:=RetRange
              .Axes(xlCategory, xlPrimary).HasTitle = True
              .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
              .Axes(xlValue, xlPrimary).HasTitle = True
              .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = secAxisTitle
              .Name = ChartSheetName
              .SetElement (msoElementLegendBottom)
              .Axes(xlCategory).TickLabelPosition = xlLow
              .Axes(xlCategory).MajorUnit = y
              .Axes(xlCategory).MajorUnitScale = xlMonths
          End With
  End Function
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  
  Function TestDates(pDate1 As Date, pDate2 As Date) As Long
  TestDates = DateDiff("m", pDate1, pDate2)
  End Function
  </code></pre>
  
  <p>Any Help would be deeply appreciated.</p>