Setting Chart Y axis to start at no less than 0

**beerbud89** · 05-02-2013, 04:04 PM

Can anyone tell me what line needs to but changed or put into this code so that my last chart with two series will read from 0 up. Some of my data in the range for the series are negative numbers and it makes the chart look ridiculous and I really don't need the negative data.

 Sub CreateCharts()
    
    Dim chts() As Variant
    Dim cObj As Shape
    Dim cht As Chart
    Dim chtLeft As Double, chtTop As Double, chtWidth As Double, chtHeight As Double
    Dim lastRow As Long
    Dim c As Long
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    lastRow = ws.Range("A1", Range("A2").End(xlDown)).Rows.Count
    
    c = -1
    '## Create an array of chart names in this sheet. ##'
    For Each cObj In ActiveSheet.Shapes
        If cObj.HasChart Then
            ReDim Preserve chts(c)
            chts(c) = cObj.Name
    
            c = c + 1
        End If
    Next
    
    '## Check to see if your charts exist on the worksheet ##'
    If c = -1 Then
        ReDim Preserve chts(0)
        chts(0) = ""
    End If
    If IsError(Application.Match("RPM", chts, False)) Then
        '## Add this chart ##'
        chtLeft = ws.Cells(lastRow, 1).Left
        chtTop = ws.Cells(lastRow + 2, 1).Top + ws.Cells(lastRow + 2, 1).Height
        Set cObj = ws.Shapes.AddChart(xlLine, chtLeft, chtTop, 355, 211)
            cObj.Name = "RPM"
            cObj.Chart.HasTitle = True
            Set cht = cObj.Chart
            cht.ChartTitle.Characters.Text = "RPM"
            clearChart cht
    End If
    
    
    If IsError(Application.Match("Pressure/psi", chts, False)) Then
        '## Add this chart ##'
        With ws.ChartObjects("RPM")
            chtLeft = .Left + .Width + 10
            chtTop = .Top
            Set cObj = ws.Shapes.AddChart(xlLine, chtLeft, chtTop, 355, 211)
            cObj.Name = "Pressure/psi"
            cObj.Chart.HasTitle = True
            Set cht = cObj.Chart
            cht.ChartTitle.Characters.Text = "Pressure/psi"
            clearChart cht
        End With
    End If
    
    
    If IsError(Application.Match("Third Chart", chts, False)) Then
        '## Add this chart ##'
        With ws.ChartObjects("Pressure/psi")
            chtLeft = .Left + .Width + 10
            chtTop = .Top
            Set cObj = ws.Shapes.AddChart(xlLine, chtLeft, chtTop, 355, 211)
            cObj.Name = "Third Chart"
            cObj.Chart.HasTitle = True
            Set cht = cObj.Chart
            cht.ChartTitle.Characters.Text = "Third Chart"
            clearChart cht
        End With
    End If
    

    
    End Sub
    
    Sub clearChart(cht As Chart)
    Dim srs As Series
    For Each srs In cht.SeriesCollection
        If Not cht.SeriesCollection.Count = 1 Then srs.Delete
    Next
    End Sub


 Sub UpdateCharts()
    Dim cObj As ChartObject
    Dim cht As Chart
    Dim shtName As String
    Dim chtName As String
    Dim xValRange As Range
    Dim lastRow As Long
    
    With ActiveSheet
        lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
        Set xValRange = .Range("B2:B" & lastRow)
        shtName = .Name & " "
    End With
    
    
    '## This sets values for Series 1 in each chart ##'
    For Each cObj In ActiveSheet.ChartObjects
        Set cht = cObj.Chart
        chtName = shtName & cht.Name
        
        If cht.SeriesCollection.Count = 0 Then
        '## Add a dummy series which will be replaced in the code below ##'
            With cht.SeriesCollection.NewSeries
                .Values = "{1,2,3}"
                .XValues = xValRange
            End With
            
        End If
        
        '## Assuming only one series per chart, we just reset the Values & XValues per chart ##'
        With cht.SeriesCollection(1)
        '## Assign the category/XValues ##'
           .XValues = xValRange
    
        '## Here, we set the range to use for Values, based on the chart name: ##'
            Select Case Replace(chtName, shtName, vbNullString)
                 Case "RPM"
                      .Values = xValRange.Offset(0, 3) '## Column E is 3 offset from the xValRange in column B
                      .Name = "RPM"
                 Case "Pressure/psi"
                      .Values = xValRange.Offset(0, 5) '## Column G is 5 offset from the xValRange in column B
                      .Name = "Pressure/psi"
                 Case "Third Chart"
                    .Values = xValRange.Offset(0, 6)   '## Column H is 6 offset from the xValRange in column B
                    .Name = "Demand burn off"
                    '## Make sure this chart has 2 series, if not, add a dummy series ##'
                    If cht.SeriesCollection.Count < 2 Then
                        With cht.SeriesCollection.NewSeries
                            .XValues = "{1,2,3}"
                            
                        End With
                    End If
                    '## add the data for second series: ##'
                    cht.SeriesCollection(2).XValues = xValRange
                    cht.SeriesCollection(2).Values = xValRange.Offset(0, 8)  '## Column J is 8 offset from the xValRange in column B
                    cht.SeriesCollection(2).Name = "Step Burn Off"
                 Case "Add as many of these Cases as you need"
    
            End Select

        End With

    Next
    End Sub

Chart2.jpg

**ExcelTab** · 05-03-2013, 03:49 AM

Hello beerbud89,

Add this to the end of your code :

cht.Axes(xlValue).MinimumScale = 0

Have fun !

**beerbud89** · 05-03-2013, 09:23 AM

Thanks ExcelTab! That worked great! There are a couple of things I would like to do to tweak my charts a little. I would like the line colors of series 1 in each chart to be blue. I have already figured out how to change the line color of series 2 in the last chart to red. Also, how can I get the third chart a little clearer? It's kind of hard to read. I've attached a jpeg of the charts and what they look like with your addition to my code.

 Sub UpdateCharts()
    Dim cObj As ChartObject
    Dim cht As Chart
    Dim shtName As String
    Dim chtName As String
    Dim xValRange As Range
    Dim lastRow As Long
    
    With ActiveSheet
        lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
        Set xValRange = .Range("B2:B" & lastRow)
        shtName = .Name & " "
    End With
    
    
    '## This sets values for Series 1 in each chart ##'
    For Each cObj In ActiveSheet.ChartObjects
        Set cht = cObj.Chart
        chtName = shtName & cht.Name
        
        If cht.SeriesCollection.Count = 0 Then
        '## Add a dummy series which will be replaced in the code below ##'
            With cht.SeriesCollection.NewSeries
                .Values = "{1,2,3}"
                .XValues = xValRange
            End With
            
        End If
        
        '## Assuming only one series per chart, we just reset the Values & XValues per chart ##'
        With cht.SeriesCollection(1)
        '## Assign the category/XValues ##'
           .XValues = xValRange
    
        '## Here, we set the range to use for Values, based on the chart name: ##'
            Select Case Replace(chtName, shtName, vbNullString)
                 Case "RPM"
                      .Values = xValRange.Offset(0, 3) '## Column E is 3 offset from the xValRange in column B
                      .Name = "RPM"
                 Case "Pressure/psi"
                      .Values = xValRange.Offset(0, 5) '## Column G is 5 offset from the xValRange in column B
                      .Name = "Pressure/psi"
                 Case "Burn Off"
                    .Values = xValRange.Offset(0, 6)   '## Column H is 6 offset from the xValRange in column B
                    .Name = "Demand burn off"
                    '## Make sure this chart has 2 series, if not, add a dummy series ##'
                    If cht.SeriesCollection.Count < 2 Then
                        With cht.SeriesCollection.NewSeries
                            .XValues = "{1,2,3}"
                            
                        End With
                    End If
                    '## add the data for second series: ##'
                    cht.SeriesCollection(2).XValues = xValRange
                    cht.SeriesCollection(2).Values = xValRange.Offset(0, 8)  '## Column J is 8 offset from the xValRange in column B
                    cht.SeriesCollection(2).Name = "Step Burn Off"
                    cht.SeriesCollection(2).Border.Color = RGB(255, 0, 0)
                 Case "Add as many of these Cases as you need"
    
            End Select

        End With
        cht.Axes(xlValue).MinimumScale = 0
        
    Next
    End Sub

Charts.jpg

**beerbud89** · 05-03-2013, 09:58 AM

Is there a way to make the series data start at zero or the first positive number instead of reading the negatives at all? That may be a better option!

**ExcelTab** · 05-03-2013, 10:43 AM

Hello beerbud89,

There is a way, but it is quite tidious (personnaly, i like your graphs the way they are).

Anyway, to change your 3rd graph, and take only positive data, one way is to recreate new array of values (here called XValues2 and Values2) and build a loop similar to the following:

Redim XValues2(0)
Redim Values2(0)
For i = 1 to XValRange.cells.countIf XValRange.cells(i,9) >=0 thenRedim Preserve XValues2(uBound(XValues2)+1)
Redim Preserve Values2(uBound(Values2)+1)
XValues2(uBound(XValues2)) = XValRange.Cells(i,1))
Values2(uBound(Values2)) = XValRange.Cells(i,9))
End if
Next i

In this code, i am assuming that you want to do this for your last column (the one you put in XValRange.offset(0,8)).

Dont forget to do the same thing for your SeriesCollection(1) values.
Then, when building your graph, use the XValues2 and Values2 array to populate them.

Have fun !

**beerbud89** · 05-03-2013, 10:56 AM

It is quite tedious. Especially because of my lack of VBA knowledge. I had help building the code so I am not sure where to place the new array or the lope. I can try to figure it out though!