CODEWITHSUNDEEP
excelvbacharts
Jose Cortez
Jose Cortez

Reputation: 93

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

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

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

How the macro is being run and information from user form

Where my information lives from the userform

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

Upvotes: 0

Views: 1518

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

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.

Upvotes: 2

Related Questions