Databases
StackOverflow2013
Postsdbo
POMake Series Skip Negative Values in Excel VBA?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMake Series Skip Negative Values in Excel VBA?
    primarykey
    data
    text
    <p>As of now this piece of code creates 3 charts. The last chart has two series. The second series starts at a negative number so I put <code>cht.Axes(xlValue).MinimumScale = 0</code> at the end of the code below to make the y-axis start at zero no matter what. The only problem is it makes my graph look jumbled. Is there anything that I can add to the existing code to make the second series skip inputting negative numbers and start with the first 0 or positive number?</p> <pre><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" &amp; .Rows.Count).End(xlUp).Row Set xValRange = .Range("B2:B" &amp; lastRow) shtName = .Name &amp; " " End With '## This sets values for Series 1 in each chart ##' For Each cObj In ActiveSheet.ChartObjects Set cht = cObj.Chart chtName = shtName &amp; 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 &amp; XValues per chart ##' With cht.SeriesCollection(1) '## Assign the category/XValues ##' .Border.Color = RGB(0, 0, 255) .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 &lt; 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 </code></pre> <p>Here is the image: <img src="https://i.stack.imgur.com/H7an1.jpg" alt="Burn off"></p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USray
    1. USTMC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMake Series Skip Negative Values in Excel VBA?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat do you mean by "it looks jumbled"? What you have posted looks fine to me. Can you be more specific about what you expected?
      singulars
      1. POMake Series Skip Negative Values in Excel VBA?
      1. USVicky
    2. COOh, hang on, I think I get it. Are you saying you don't want the "demand burn off" series to show at all for the first entries (where "step burn off" is negative), so the whole chart would start at 15:11:37 on the X axis?
      singulars
      1. POMake Series Skip Negative Values in Excel VBA?
      1. USVicky
    3. COExactly, so the x axis should start closer 15:11:37 if the negative input was skipped. It just smashes the line the way it is now because I set the Y axis to start at zero.
      singulars
      1. POMake Series Skip Negative Values in Excel VBA?
      1. USTMC
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload