VBA Chart Blank Values in Cell Range

Question

How the cart should be displayed with information, it is being displayed correctly because I added a zero value in cell B16

The error chart, not displaying properly because mortgage1 doesn't have a zero value in its cell

How the macro is being run and information from user form

Where my information lives from the userform

I have a chart that is generated with a macro for a range of cells. At times some blank cells will exist within that range. I'm facing a problem when generating my chart with an empty cell value, the chart will start plotting at the first non empty cell, causing the category axis to not be reporting correct. How can I make my chart show a blank value on my chart using VBA. I would prefer not to use a formula on my cell range.

Here is my code:

Sub createchart()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets("contactunder")


Dim CellRow As Integer    

wb.Sheets("ContactsFront").Select  
CellRow = ActiveCell.Row   
Worksheets("samplesheet").Activate
ActiveSheet.Shapes.AddChart2(251, xlBarClustered).Select
ActiveChart.DisplayBlanksAs = xlNotPlotted
ActiveChart.SetSourceData Source:=ws.Range("BY" & CellRow & ",CB" & CellRow & ",CH" & CellRow & ",CJ" & CellRow & ",CL" & CellRow & ",CN" & CellRow & ",CP" & CellRow)
ActiveChart.FullSeriesCollection(1).XValues = "contactunder!$BY$10,contactunder!$CB$10,contactunder!$CH$10,contactunder!$CJ$10,contactunder!$CL$10,contactunder!$CN$10,contactunder!$CP$10"
ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesNone)
ActiveChart.Axes(xlValue).MajorGridlines.Format.Line.Visible = msoFalse
ActiveChart.FullSeriesCollection(1).DataLabels.ShowCategoryName = False

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Analysis for " & ws.Range("D" & CellRow)
ActiveChart.HasAxis(xlValue) = False
ActiveChart.HasLegend = False
End With

End Sub

Have you tried setting ActiveChart.DisplayBlanksAs to xlInterpolated or xlZero? — cybernetic.nomad
@cybernetic.nomad unfortunately neither of those options have provided a solution — Jose Cortez
Can you post an example the expected result and of what you get, so we can compare? — cybernetic.nomad
@cybernetic.nomad please see my post for included pictures. Hope this helps. Thanks again for your continued help! — Jose Cortez
Since you are using union range as source data of your chart, all empty cells in that union range will not be into the source. So there must not be empty cells in that union range. Cells without values must be #N/A. — Axel Richter

Axel Richter Axel Richter · Accepted Answer · 2018-07-01T07:54:53

Since you are using union range as source data of your chart, first blank cell in that union range will not be into the source after setting that union range as the source data of the chart using SetSourceData.

So first cell must not be empty in that union range. Cells without values must be #N/A.

You could replacing blank with #N/A within your code like so:

...
Set oChartRange = ws.Range("BY" & CellRow & ",CB" & CellRow & ",CH" & CellRow & ",CJ" & CellRow & ",CL" & CellRow & ",CN" & CellRow & ",CP" & CellRow)
For Each oCell In oChartRange
 If Not IsError(oCell.Value) Then
  If oCell.Value = "" Then oCell.Value = CVErr(2042)
 End If
Next
ActiveChart.SetSourceData Source:=oChartRange
...

Of course then you need keeping in mind that there are error values possible in that cell when using that cell in formulas. So maybe you must using IFERROR then. But nevertheless I would not using 0 because 0 is a valuable number and it's meaning is different from blank. Blank means not available rather than 0. So I would really using #N/A here.

VBA Chart Blank Values in Cell Range

1 Answers