Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jun 2012
    Thanked 0 Times in 0 Posts

    VBA stop graphing series with no data

    I have typed up a code that works perfect. I use cell.find function and it graphs the data, but sometimes there is no data and then it posts its Series name still

    How do I make it not post its series name

    Sub Tool_Pressure_Graph()
    ' Tool Pressure Load Cell Graph
        Dim xaxis As Range
        Dim yaxis As Range
        On Error Resume Next
      LR = Range("A" & Rows.Count).End(xlUp).Row
      myLoad1 = Cells.Find("Tool Pressure Load Cell #1").Address
      myLoad2 = Cells.Find("Tool Pressure Load Cell #2").Address
      myLoad3 = Cells.Find("Tool Pressure Load Cell #3").Address
      myLoad4 = Cells.Find("Tool Pressure Load Cell #4").Address
      myLoad5 = Cells.Find("Tool Pressure Load Cell #5").Address
      myLoad6 = Cells.Find("Tool Pressure Load Cell #6").Address
        Set xaxis = Range("$B$5", Range("data!$B$5" & LR))
        Set yaxis1 = Range(myLoad1, Range(myLoad1 & LR))
        Set yaxis2 = Range(myLoad2, Range(myLoad2 & LR))
        Set yaxis3 = Range(myLoad3, Range(myLoad3 & LR))
        Set yaxis4 = Range(myLoad4, Range(myLoad4 & LR))
        Set yaxis5 = Range(myLoad5, Range(myLoad5 & LR))
        Set yaxis6 = Range(myLoad6, Range(myLoad6 & LR))
        With Charts.Add
            Do While .SeriesCollection.Count > 0
                ' remove any series added automatically
            With .SeriesCollection.NewSeries
                .Values = yaxis1
                .XValues = xaxis
             End With
            With .SeriesCollection.NewSeries
                .Values = yaxis2
                .XValues = xaxis
             End With
            With .SeriesCollection.NewSeries
                .Values = yaxis3
                .XValues = xaxis
             End With
            With .SeriesCollection.NewSeries
                .Values = yaxis4
                .XValues = xaxis
             End With
            With .SeriesCollection.NewSeries
                .Values = yaxis5
                .XValues = xaxis
             End With
            With .SeriesCollection.NewSeries
                .Values = yaxis6
                .XValues = xaxis
             End With
            .ChartType = xlXYScatterSmoothNoMarkers
        End With
        ActiveChart.Location Where:=xlLocationAsNewSheet, name:= _
            "Tool Pressure Load Cell"
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Tool Pressure Load Cell"
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, _
                    xlPrimary).AxisTitle.Characters.Text = "Time [s]"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, _
                    xlPrimary).AxisTitle.Characters.Text = "Tool Pressure Load Cell [psi]"
        End With
        With ActiveChart.Axes(xlValue)
            .MinimumScaleIsAuto = True
            .MaximumScaleIsAuto = True
            .MinorUnitIsAuto = True
            .MajorUnitIsAuto = True
            .Crosses = xlAutomatic
            .ReversePlotOrder = False
            .ScaleType = xlLinear
            .DisplayUnit = xlNone
        End With
            ' Plots and formats the chart
    End Sub

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 229 Times in 210 Posts
    Are the value cells empty, or do they contain formulas, or 0, or other?

    Microsoft MVP - Excel

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts