title: Automatically generate a stacked bar chart with Dynamic Ranges?
body: <p>I am looking to develop a tool for my coworkers to use that automatically generates a stacked bar chart.  I need this to work for any sized data set, for any number of <code>Product</code> and any number of <code>Part</code>.  So, I need an auto-adjusting VBA range for my macro, and I'm not sure how to do it.  </p>
  
  <p>It needs to do the following:</p>
  
  <ul>
  <li>Takes the SUM of values in a row, and logs it at the column on the end.</li>
  <li>Creates a stacked bar chart for each of the rows. </li>
  <li><p>Go to "Select Data" and "Switch Rows and Columns"</p></li>
  <li><p>Turns the <code>Total</code> data values to <code>No Fill</code></p></li>
  <li>Makes the <code>Total</code> data labels show up "Inside Base"</li>
  <li>removes the white space from the stacked bar chart by changing the maximum of the Y-Axis.</li>
  </ul>
  
  <p>My problems are the following:</p>
  
  <ul>
  <li>The recording of the Macro didn't record setting the <code>Total</code> data selection to No Fill.  How should I do this for just the <code>Total</code> data points?</li>
  <li>How do I use VBA to switch the rows and columns? </li>
  <li>And then finally, I am not sure how to make this macro work for any sized data set?</li>
  </ul>
  
  <p>Here is a sample data set that I have been practicing on:
  <a href="https://docs.google.com/spreadsheets/d/19n_UH2fJxomur13G_I89WRaqW93KH6Z0FIpUOzsciyE/edit#gid=0" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/19n_UH2fJxomur13G_I89WRaqW93KH6Z0FIpUOzsciyE/edit#gid=0</a></p>
  
  <p>Here is the type of chart that I want to create:
  <a href="https://i.stack.imgur.com/X5iAq.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/X5iAq.jpg" alt="enter image description here"></a></p>
  
  <p>And here is the macro that was created when I did it manually:</p>
  
  <pre><code>    Sub StackedBar1()
  '
  ' StackedBar1 Macro
  '
  
  '
      Range("G2").Select
      ActiveCell.FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
      Range("G3").Select
      ActiveCell.FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
      Range("G4").Select
      ActiveCell.FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
      Range("A1:G4").Select
      ActiveSheet.Shapes.AddChart.Select
      ActiveChart.SetSourceData Source:=Range("'Sheet3'!$A$1:$G$4")
      ActiveChart.ChartType = xlColumnStacked
      ActiveChart.SetSourceData
      ActiveChart.SetElement (msoElementDataLabelCenter)
      ActiveChart.SeriesCollection(6).Select
      ActiveSheet.ChartObjects("Chart 4").Activate
      ActiveChart.SeriesCollection(6).DataLabels.Select
      ActiveSheet.ChartObjects("Chart 4").Activate
      Selection.Position = xlLabelPositionInsideBase
      ActiveSheet.ChartObjects("Chart 4").Activate
      ActiveChart.Axes(xlValue).Select
      ActiveSheet.ChartObjects("Chart 4").Activate
      ActiveChart.Axes(xlValue).MaximumScale = 14
      ActiveChart.Axes(xlValue).MaximumScale = 8
      ActiveSheet.ChartObjects("Chart 4").Activate
      ActiveChart.ChartArea.Select
  End Sub
  </code></pre>