Plot On Chart/Graph As Zigzag Pattern

[giloopp]

Hi!
I have the following list of values which I want to plot as a zigzag. But when i create a line chart from them, the lines connect the zeros and the non-zero points - while I want the lines to connect only the non-zero points - so that there is a zigzag-like pattern shown...I wonder if this can be done..Thanks!

the Data:

0
0
0
0
0
1.4343
0
0
0
0
0
0
0
0
1.4332
0
0
0
0
0
0
0
0
1.4318
0
0
0
0
0
0
0
0
0
1.4521
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1.5153
0
0
0
0
0
0
1.5336
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1.55
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1.5431
0
0
0
0
0
0
0
0
0
1.5472
0
0
0
0
0
0
0
0
1.5404
0
0
0
0
0
0
0


Dima

[Yard]

If you want to plot the non-zero values then put an autofilter on your data, set it to Custom > does not equal 0.

Charts only plot visible data.

BTW shouldn't your chart have a second data series, as the non-zero values are not equally spaced?

[junho lee]

hi,
i numbered data and make scattered chart.
have a look at attached file.
regards/junho lee

[giloopp]

thanks..but i need such a graph without modifying the data series...((

[junho lee]

Option Explicit 
Option Base 0 
Sub create_chart() 
     '--------------------set data source
    Dim data, i As Long, num As Long, data1, data2 
    data = Range("a1:a" & Range("a65536").End(xlUp).Row) 
    Redim data1(num) 
    Redim data2(num) 
    For i = 1 To UBound(data, 1) 
        If data(i, 1) > 0 And IsNumeric(data(i, 1)) Then Redim Preserve data1(num) _ 
        : Redim Preserve data2(num) _ 
        : data1(num) = i: data2(num) = data(i, 1): num = num + 1 
    Next 
     '----------------------make chart
    Charts.Add 
    ActiveChart.ChartType = xlXYScatter 
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" 
    ActiveChart.SeriesCollection.NewSeries 
    ActiveChart.SeriesCollection(1).XValues = WorksheetFunction.Transpose(data1) 
    ActiveChart.SeriesCollection(1).Values = WorksheetFunction.Transpose(data2) 
    ActiveChart.Axes(xlCategory).Select 
    With Selection.Border 
        .Weight = xlHairline 
        .LineStyle = xlAutomatic 
    End With 
    With Selection 
        .MajorTickMark = xlOutside 
        .MinorTickMark = xlNone 
        .TickLabelPosition = xlNextToAxis 
    End With 
    Selection.TickLabels.AutoScaleFont = False 
    With Selection.TickLabels.Font 
        .Size = 11 
    End With 
    ActiveChart.Axes(xlValue).Select 
    With Selection.Border 
        .Weight = xlHairline 
        .LineStyle = xlAutomatic 
    End With 
    With Selection 
        .MajorTickMark = xlOutside 
        .MinorTickMark = xlNone 
        .TickLabelPosition = xlNextToAxis 
    End With 
    Selection.TickLabels.AutoScaleFont = False 
    With Selection.TickLabels.Font 
        .Size = 11 
    End With 
    ActiveChart.SeriesCollection(1).Select 
    With Selection.Border 
        .Weight = xlThin 
        .LineStyle = xlAutomatic 
    End With 
    With Selection 
        .MarkerBackgroundColorIndex = xlAutomatic 
        .MarkerForegroundColorIndex = xlAutomatic 
        .MarkerStyle = xlAutomatic 
        .Smooth = False 
        .MarkerSize = 5 
        .Shadow = False 
    End With 
    ActiveChart.Legend.Select 
    Selection.Delete 
End Sub 

regards/junho lee

[Yard]

If you can't modify your data then can you add some further calculations alongside it?

The attached gives you what you want without changing or filtering your data.

[giloopp]

thanks....let me check it out!