Create Excel chart from merged cells
in [Charts]
|
Prev: How do I easily label 200 different data points on a buble cha
Next: Secondary axis not aligning to X axis
From: Sean on 7 Feb 2010 05:34 I was looking for an answer to creating a chart from merged cells. After searching and not finding an answer I came across the Union method. This sub creates a chart for a series of two merged cells expanding across the whole worksheet apart for the first two cells Sub subCreateChart() 'This sub creates a chart for merged cells taking the information from the cell listed 'eg the two merged cells C37 and D37 the Excel address is C37 that holds the data. Dim rngAvg As Range Dim rngTemp As Range Dim i As Integer For i = 3 To 253 Step 2 Set rngTemp = Range(Cells(37, i), Cells(39, i)) 'Set the first and subsequent merged cells as a range. If i = 3 Then Set rngAvg = rngTemp 'set the first selected range to an accumulating range Else Set rngAvg = Union(rngAvg, rngTemp) 'after the first range has been assigned to rngAvg 'add all over ranges by looping through the required data. End If Next i Charts.Add ActiveChart.ChartType = xlLine ActiveChart.SetSourceData rngAvg, PlotBy:=xlRows 'when collection is complete use the accuumulated 'range to create chart. ActiveChart.DisplayBlanksAs = Excel.XlDisplayBlanksAs.xlInterpolated ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub Bernard Liengme wrote: Merged cells are to be avoided at all costs. 08-Oct-09 Merged cells are to be avoided at all costs. They can be used for column heading but for little else. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email Previous Posts In This Thread: On Thursday, October 08, 2009 12:34 PM Troubled excel user wrote: merged cells and creating charts I have tried to create a chart with data that is in merged cells where I have put in a formula to calculate the average of 4 cells in the row above. When I create the chart, it recognizes all 4 of the merged cells. So the graph has 4 spaces for the data for each number I am trying to plot. I just got 2007 version and do not recall this problem at all in 2003 version On Thursday, October 08, 2009 12:39 PM Bernard Liengme wrote: Merged cells are to be avoided at all costs. Merged cells are to be avoided at all costs. They can be used for column heading but for little else. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email On Thursday, October 08, 2009 12:53 PM David Biddulph wrote: I'd be willing to guess that Excel 2003 would have behaved the same. I'd be willing to guess that Excel 2003 would have behaved the same. Thank you for adding yet another example to the countless demonstrations (listed in the archives of these Excel newsgroups) of why merged cells should be avoided like the plague. We keep advising people, but they keep shooting themselves in the foot by using merged cells when there are usually much better alternatives (such as the alignment option to "Center across selection"). -- David Biddulph On Thursday, October 08, 2009 3:24 PM Jon Peltier wrote: The online help should say in size 16 type:DON'T USE MERGED CELLS- The online help should say in size 16 type: DON'T USE MERGED CELLS - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ David Biddulph wrote: Submitted via EggHeadCafe - Software Developer Portal of Choice WMI - VB 6.0 Windows Management Instrumentation http://www.eggheadcafe.com/tutorials/aspnet/5fb2b0ca-919a-406b-8566-a9d07f76c868/wmi--vb-60-windows-mana.aspx |