Missing portion of the graph in VBA when 2 or more separate graphs are plotted when same data are used

Question

When I plot my chart using one set of data(current 1), the graph shows the correct output. However when I plot 2 sets of data concurrently(current 1 and current 2), part of the graph is missing(circled in red) for both data consisting of current 1 and current 2. Btw the data are the same for both scenarios and no data are missing. As my data for them is huge, I can only show you the part of my data sample which looks like this as shown below for current 1 and current 2. I know the code for plotting graph of current1 only contains a lot of variables that you all might deem as the one that causes problem so let me clarify that totalsample1 and myarray values should be correct as they are the ones responsible for the data(Like the one shown in data sample for current1) for plotting the graph for current and all data for plotting are present in this case. So what could be the code that causes this problem and how to remedy it?

Data sample for current 1

Data sample for current 2

1st update:add wsf to range and cells

2nd update: replaced activechart with cht1

3rd update: Remove from For i = 2 To totalsample1 Step 1 till all the cht1.series collection

4th update: I make a simpler version of my code to plot graph for current 1.

5th update: And using the code suggested by @Dy.Lee, the ideal graph for current 1 is as shown.

Private Sub addgraph_Vramp1()
  Application.ScreenUpdating = False
        Dim i As Long

    Dim wf As Workbook
    Set wf = ActiveWorkbook
    Dim wsf As Worksheet
    Set wsf = wf.Worksheets("current1")
     Dim shp1 As Shape
   Dim Cht1 As Chart

   Set shp1 = wsf.Shapes.AddChart
   Set Cht1 = shp1.Chart
    wsf.Activate
    With Cht1
   Cht1.SetSourceData Source:=wsf.Range("A1:BQ750")
   Cht1.ChartType = xlXYScatterSmoothNoMarkers
    Cht1.Axes(xlValue).ScaleType = xlLogarithmic
    Cht1.Axes(xlValue).MaximumScale = 0.001
    Cht1.Axes(xlValue).MinimumScale = 0.000000000000001
   End With

    With Cht1
        .Legend.Delete
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Voltage"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Current"
    End With

changes to the graph for the current 1 after using updated codes

changes to the graph after removing cht1.series collection(it still remains in the updated code just to let other knows what is being removed here)

Ideal graph :))))

There are several issues with your code: use of `Integer` ([should be `Long`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long)); `Range(Cells(...), Cells(...))` is [problematic](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells)... but as is this question might need more detail to be able to get to a [reprex]. — BigBen, Feb 11 '20 at 04:25
I believe at least one of your problems is that your source range is ALL of columns A and B. You should determine exactly how many rows have the data (1,000? 10,000?) and create a realistic range to use instead of `ActiveChart.SetSourceData Source:=Range("'current1'!$A:$B")`. I'll also call attention to the [problematic](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) issue raised by @BigBen. It's very possible your ranges are referencing different worksheets than you think. — PeterT, Feb 11 '20 at 04:35
@PeterT lets say i have 100 rows of data,100 rows of columns for current1 data, how should i write to replace my ActiveChart.SetSourceData Source:=Range("'current1'!$A:$B")? — cena, Feb 11 '20 at 04:50
@Bigben so i assume i should be doing something like this to Dim wb As Workbook Set wb = ActiveWorkbook Dim wss As Worksheet Set wss = wb.Worksheets("current1") to replace my range(Cells() with wss.range(wss.cells(..), wss.cells(...)? — cena, Feb 11 '20 at 05:07
See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row and column. — BigBen, Feb 11 '20 at 05:13
@BigBen i feel like the code that is causing the problem right now is ActiveChart.SetSourceData Source:=Range("'current1'!$A:$B") with that being said, a line is being plotted where every line constitutes of data from column A represents the voltage, column B represents the current and after that the next line is being plotted which constitutes of data of voltage from column C, current for column D and so on. But the thing is how do i use the With Sheets("current1") LastRow = .Range("E" & .Rows.Count).End(xlUp).Row End With and making sure that the above still holds? — cena, Feb 11 '20 at 05:20
@Bigben Wait isn't ActiveChart.SetSourceData Source:=Range("'current1'!$A:$B") only referring to data for column A and column B? — cena, Feb 11 '20 at 05:49
I have updated my codes based on @BigBen and PeterT suggestions, there are more lines added to the graph(which i think the previously missing ones) and i can see a slight improvement to the graph.But i realised as i increased my range as such from A1:BQ701, because i am plotting logarithmic graph, the error message "negative or zero values cannot be plotted appear which does not happen previously tho.. What else i can do to further improve? — cena, Feb 11 '20 at 06:30
After removing all the cht1.series collection codes(im not sure why it should be removed tho), there are more correct lines appearing now. So what should i change to extend my range such that beyond voltage =60, there will be graph plotted? — cena, Feb 11 '20 at 09:26

Dy.Lee · Accepted Answer · 2020-02-11T14:59:34.767

Caught wrong range

     Cht1.SeriesCollection(i).XValues = wsf.Range(Cells(2, 2 * i - 1), wsf.Cells(myarray(i + 1), 2 * i - 1))

To

     Cht1.SeriesCollection(i).XValues = wsf.Range(wsf.Cells(2, 2 * i - 1), wsf.Cells(myarray(i + 1), 2 * i - 1))

This is an example of creating a chart using a parameterized procedure.

Sub test()
    Dim Ws As Worksheet
    Dim Ws2 As Worksheet

    Set Ws = Sheets("current1")
    Set Ws2 = Sheets("current2")

    addgraph_Vramp1 Ws
    addgraph_Vramp1 Ws2

End Sub

Private Sub addgraph_Vramp1(Ws As Worksheet)

    Dim i As Long, c As Long
    Dim shp As Shape
    Dim Cht As Chart
    Dim rngDB As Range, rngX As Range, rngY As Range
    Dim Srs As Series

    Set rngDB = Ws.UsedRange

    c = rngDB.Columns.Count

    Set shp = Ws.Shapes.AddChart
    Set Cht = shp.Chart

    With Cht
        .ChartType = xlXYScatterSmoothNoMarkers
        .HasLegend = False
        For Each Srs In .SeriesCollection
            Srs.Delete
        Next Srs
        For i = 1 To c Step 2
            With Ws
                Set rngX = Ws.Range(.Cells(2, i), .Cells(2, i).End(xlDown))
                Set rngY = rngX.Offset(, 1)
            End With
            Set Srs = .SeriesCollection.NewSeries
            With Srs
                .XValues = rngX
                .Values = rngY
            End With
        Next i
        .Axes(xlValue).ScaleType = xlLogarithmic
        .Axes(xlValue).MaximumScale = 0.001
        .Axes(xlValue).MinimumScale = 0.000000000000001
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Voltage"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Current"
    End With

End Sub

hey thanks for pointing that out but still not much changes tho.. — cena, Feb 11 '20 at 09:05

Missing portion of the graph in VBA when 2 or more separate graphs are plotted when same data are used

1 Answers1