Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jun 2012
    Posts
    3
    Thanks
    0
    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


    Code:
    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
                .SeriesCollection(1).Delete
            Loop
            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
        ActiveChart.Axes(xlValue).MajorGridlines.Select
        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
    Thanks

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Are the value cells empty, or do they contain formulas, or 0, or other?
    Regards,
    Rory

    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
  •