VBA Chart Blank Values in Cell Range

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


ActiveChart.DisplayBlanksAs

xlInterpolated

xlZero

#N/A

1 Answer
1

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.

SetSourceData

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

#N/A

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

#N/A

...
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.

IFERROR

#N/A

.text

#N/A

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.