Chart macro has problems with data gaps

[artz]

Hi

I am having problem with a macro used to create a line chart. There are four Y series plotted. Because two of the Y series are moving averages, there are some number of blank cells determined by the moving average period before the moving average data are displayed.

Both the moving average series plot out coincident with the first x value. There should instead be a gap before the data are plotted. I tried the feature "Show empty Cells as Gaps" but it does nothing. Maybe this is because the chart is created using VBA. Below is the code used to create the chart:

Sub CreateChart()

    Dim MyChart As Chart
    Dim MyRange As Range
    Dim LastRow As Long
    Dim srs As Series
 
    Application.ScreenUpdating = False
 
    On Error Resume Next
    Sheets("Chart").Select
    ActiveSheet.ChartObjects.Select
    ActiveSheet.ChartObjects.Delete
    On Error GoTo 0
    
    LastRow = Worksheets("Data").Cells(Rows.Count, "E").End(xlUp).Row
    
    If LastRow < 2 Then
        MsgBox "No data is available...", vbInformation
        Exit Sub
    End If
    
    Set MyRange = Worksheets("Data").Range("E2:E" & LastRow & ",K2:K" & LastRow & ",R2:R" & LastRow & ",S2:S" & LastRow & ",T2:T" & LastRow)
    
    Set MyChart = Worksheets("Chart").Shapes.AddChart(xlLine).Chart
    
     MyChart.SetSourceData Source:=MyRange, PlotBy:=xlColumns
       
Worksheets("Chart").ChartObjects(1).Activate
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Inverse Stock Pair"

Set srs = ActiveChart.SeriesCollection(1)
srs.Name = Worksheets("Chart").Range("B1").Value
Set srs = ActiveChart.SeriesCollection(2)
srs.Name = Worksheets("Chart").Range("C1").Value

With ActiveChart

.ChartTitle.Font.Size = 12
.ChartTitle.Font.Name = "Arial Unicode MS"
.SeriesCollection(1).Format.Line.Weight = 1.25
.SeriesCollection(2).Format.Line.Weight = 1.25
.Axes(xlCategory).TickLabels.Orientation = 45

End With

     With ActiveChart.Parent
         .Height = 325 ' resize
         .Width = 500  ' resize
         .Top = 75    ' reposition
         .Left = 275   ' reposition
     End With

With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
.MajorTickMark = xlNone
.MinorTickMark = xlNone
End With

With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
.MajorTickMark = xlNone
.MinorTickMark = xlNone
End With
    
Application.ScreenUpdating = True

  Call ScaleCharts
    
End Sub

Can anyone suggest a code change that will allow for the gaps in the data in columns S and T and plot the data correctly?

Thanks,

Art

 

[artz]

Hi All,

Well I solved my own problem but the answer was not what I expected. Here's something that I never knew. If you have a line chart (don't know about others), where the x data is arranged from newest to oldest in the column, and add Y data with a starting data gap, it does not plot correctly.

The simple solution was to sort the data in ascending order so that the last date is in the last row. Then I called the chart macro. Works perfectly. Wanted folks to know what I found.

Thanks,

Art