Av8tordude
Well-known Member
- Joined
- Oct 13, 2007
- Messages
- 1,075
- Office Version
- 2019
- Platform
- Windows
I have this code that plots points on a graph. It works reasonable well except, it plots empty spaces that coincide with the weekends (Sat & Sun) (ex 06/07-06/10). Not sure why it does this, but I would like the chart to be seamless (Don't plot the empty spaces). Is this possible?
Thanks
<colgroup><col span="5"></colgroup><tbody>
</tbody>
Thanks
Code:
Sub CreateCandlestick() Dim nRows As Integer
Dim ch As ChartObject
nRows = Sheets("Data").UsedRange.Rows.Count
'Delete existing charts
For Each ch In Sheets("Parameters").ChartObjects
ch.Delete
Next
nRows = Sheets("Data").UsedRange.Rows.Count
'Create candlestick chart
Dim OHLCChart As ChartObject
Set OHLCChart = Sheets("Parameters").ChartObjects.Add(Left:=Range("i5").Left, Width:=400, Top:=Range("i5").Top, Height:=250)
With OHLCChart.Chart
.SetSourceData Source:=Sheets("Data").Range("a1:e" & nRows)
.ChartType = xlStockOHLC
.HasTitle = True
.ChartTitle.Text = "Candlestick Chart for " & Sheets("Parameters").Range("ticker")
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price"
.HasLegend = False
.PlotArea.Format.Fill.ForeColor.RGB = RGB(220, 230, 241)
.ChartArea.Format.Line.Visible = msoFalse
.Parent.Name = "OHLC Chart"
End With
End Sub
6/3/2019 | 199.869995 | 202.699997 | 196 | 197.419998 |
6/4/2019 | 200.839996 | 223.369995 | 200.5 | 222.910004 |
6/5/2019 | 224.580002 | 234 | 224.580002 | 232.300003 |
6/6/2019 | 233 | 238.028 | 224.360001 | 237.259995 |
6/7/2019 | 239 | 250 | 238.009995 | 245.039993 |
6/10/2019 | 249.360001 | 257.130005 | 247.479996 | 249.880005 |
6/11/2019 | 253.619995 | 255.490005 | 245.869995 | 249.360001 |
6/12/2019 | 237.399994 | 245.789993 | 231.649994 | 242.199997 |
6/13/2019 | 242.850006 | 248.990005 | 242.210007 | 246.880005 |
6/14/2019 | 243.639999 | 246.880005 | 240.759995 | 244.940002 |
6/17/2019 | 246.529999 | 253.929993 | 246.169998 | 248.110001 |
6/18/2019 | 252.570007 | 255.5 | 244.5 | 245.179993 |
6/19/2019 | 247.539993 | 251.710007 | 243.899994 | 251.300003 |
<colgroup><col span="5"></colgroup><tbody>
</tbody>