Creating a Chart that Excludes Empty Cells in the Range

Question

My question here concerns charting and excluding cells in a range that are blank. I have a program that in the end will pull from a data sheet to another sheet so I can make a chart from the two pieces of data I am using (Date and Quantity). My problem is that my range length will change depending on the imput value of the data. So, some imputs will have a range from B2:C5 while others will have some as long as B2:C40. This is what I have to create my chart currently:

Sub ChartMagic()
Dim cht As Chart

Worksheets(3).Shapes.AddChart.Select
With Worksheets(3).ChartObjects(1)
    .Left = Range("A9").Left
    .Top = Range("A9").Top
End With

With ActiveChart
    .SetSourceData Source:=Worksheets(4).Range("B2:C100"), PlotBy:=xlColumns
    .ChartType = xlLine
    .HasTitle = False
    .Axes(xlCategory).HasTitle = True
    .Axes(xlCategory).AxisTitle.Text = "Date"
    .Axes(xlValue).HasTitle = True
    .Axes(xlValue).AxisTitle.Text = "Bin Quantity"
    .HasLegend = False
End With
End Sub

The problem I have with this is not that I do not create a chart but my chart includes all of the cells that are blank. Essentially, what I would like to achieve is no matter the range of cells that I bring to the "B2:C100" it will exclude the blank cells and create a chart with just the data that has a value.

a simple way to do this would be to autofilter the range to exclude blank cells. A chart in Excel only displays data from *visible* range. — David Zemens, Jul 01 '14 at 14:10
Can I do this by adding to my chart code or would that have to be done manually? — JBlake92, Jul 01 '14 at 14:14
You can add to your chart code. Hold on and I will make an answer for you. — David Zemens, Jul 01 '14 at 14:16

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

I made a few other changes so as to avoid relying on Select/Activate methods (see here for explanation why).

Revised from comments to assume a continuous range of data beginning in B2:C2.

Sub ChartMagic()
Dim cht As Chart
Dim chtDataRange as Range


'## Define the range to represent in the chart, this is dynamic and assumes that 
'   the data is contiguous (i.e., no blank rows in the middle, only at the end)
With Worksheets(4)
    Set chtDataRange = .Range("B2:C2", .Range("B2:C2").End(xlDown))
End With


'## Made some modifications to avoid using Select/Activate methods
'   use the variable "cht" to represent the Chart:  
Set cht = Worksheets(3).Shapes.AddChart
With cht
    .Left = Range("A9").Left
    .Top = Range("A9").Top
    '## Use the range variable instead of an absolute reference:
    .SetSourceData Source:=chtDataRange, PlotBy:=xlColumns
    .ChartType = xlLine
    .HasTitle = False
    .Axes(xlCategory).HasTitle = True
    .Axes(xlCategory).AxisTitle.Text = "Date"
    .Axes(xlValue).HasTitle = True
    .Axes(xlValue).AxisTitle.Text = "Bin Quantity"
    .HasLegend = False
End With


End Sub

edited May 23 '17 at 12:05

Community

1
1

answered Jul 01 '14 at 14:22

David Zemens

53,033
11
81
130

Thank you for this! It is still including the blank areas and the filter is not including B2 and C2. I am going to play with it but I believe you are on the right track – JBlake92 Jul 01 '14 at 14:38
Change to `Set chtDataRange = Worksheets(4).Range("B1:C100")` – David Zemens Jul 01 '14 at 14:46
I did that and it worked to include B2 and C2. Is there anything else I can put in the Criteria to exempt blanks? – JBlake92 Jul 01 '14 at 14:49
are there intermittent blanks in *both* columns, or only one column? – David Zemens Jul 01 '14 at 15:01
Neither. I am copying data from another sheet and some is longer than others. All the data is in order with no blanks in the middle but at the end there could be 50 blank lines or whatever depending on the amount of rows used. – JBlake92 Jul 01 '14 at 15:07
OK let's do a different approach then, I will revise. – David Zemens Jul 01 '14 at 15:10
Try now, no need for Autofilter at all. – David Zemens Jul 01 '14 at 15:13
Thank you sir. So the previous code was for if there was intermittent blanks in the columns?? – JBlake92 Jul 01 '14 at 15:17
Yes, I think so. Might not have been perfect/complete though but the idea is that a chart ordinarily will not display a hidden row. It has been a while since I did charting in Excel, I am mostly in VBA PowerPoint these days... – David Zemens Jul 01 '14 at 15:22

Creating a Chart that Excludes Empty Cells in the Range

1 Answers1