merged cells and creating charts

  • Thread starter Troubled excel user
  • Start date
T

Troubled excel user

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
 
B

Bernard Liengme

Merged cells are to be avoided at all costs.
They can be used for column heading but for little else.
best wishes
 
D

David Biddulph

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").
 
S

Sean Mulvihill

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 wishe
-
Bernard V Liengm
Microsoft Excel MV
http://people.stfx.ca/bliengm
remove caps from email

Previous Posts In This Thread:

merged cells and creating charts
I have tried to create a chart with data that is in merged cells where I hav
put in a formula to calculate the average of 4 cells in the row above. When
create the chart, it recognizes all 4 of the merged cells. So the graph has
spaces for the data for each number I am trying to plot. I just got 200
version and do not recall this problem at all in 2003 version

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 wishe
-
Bernard V Liengm
Microsoft Excel MV
http://people.stfx.ca/bliengm
remove caps from email

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 demonstration
(listed in the archives of these Excel newsgroups) of why merged cell
should be avoided like the plague
We keep advising people, but they keep shooting themselves in the foot b
using merged cells when there are usually much better alternatives (such a
the alignment option to "Center across selection")
-
David Biddulph

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 CELL

- Jo
------
Jon Peltie
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/tutorial...566-a9d07f76c868/wmi--vb-60-windows-mana.aspx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top