title: Creating a Chart that Excludes Empty Cells in the Range
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>