title: Create charts using dynamic ranges
body: <p>I'm having some problems using VBA in Excel to create charts using dynamic ranges. What I need to do is chart the results of each of the compounds for each sample. Both the number of samples and the number of compounds can vary. An example of the datasheet can be found <a href="https://drive.google.com/file/d/0B61sWPm5X9Y1T2RjM1F3djZacEE/view?usp=sharing" rel="nofollow noreferrer">here</a>.</p>
  <p>A sample of the code I tried to write, however my range has not been properly defined:</p>
  <pre><code>Sub Graph()
  
  Dim r As Range
  Dim c As Range
  Dim wks As Worksheet
  
  Set wks = ActiveSheet
  
  '
  ' Graph Macro
  '
  Set r = Range(&quot;B2&quot;).End(xlDown)
  Set c = Range(&quot;D2&quot;).End(xlToRight)
  
  ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
  ActiveChart.SetSourceData Source:=Range(&quot;wks!$B$2:B&quot; &amp; r, &quot;wks!$P$2:P&quot; &amp; c)
  '
  
  End Sub
  </code></pre>
  <p>Could anyone point me in the right direction?</p>