PDA

View Full Version : Plot area resize to fit the entire chart area



mano28
10-04-2015, 01:11 PM
Hello everyone,

I want to create Graphs with the plot area equalling the whole chart area (without any legend headings or axis) so I wrote the following code specifying the width and height as the range of the cells for the chart. The problem is the still the size of the plot area is bit lesser than the size of the chart area. I can not specify the exact width and height as they vary with different charts. Any help in this regard will be helpful.



Set chart_ist1 = ActiveSheet.Shapes.AddChart.Chart
With chart_ist1
For z1 = chart_ist1.SeriesCollection.Count To 2 Step -1
chart_ist1.SeriesCollection(z1).Delete
Next
'actual
.SetSourceData Source:=Range("E41:J41,E43:J43")
.ChartType = xlColumnClustered
.SeriesCollection(1).ApplyDataLabels
.SeriesCollection(1).DataLabels.Select

Selection.Font.Color = vbBlue
Selection.Font.Size = 6
Selection.Font.Bold = True
'Target
.SeriesCollection(2).MarkerStyle = xlMarkerStyleDiamond
.SeriesCollection(2).ChartType = xlXYScatter
.SeriesCollection(2).MarkerSize = 4
.SeriesCollection(2).ApplyDataLabels
.SeriesCollection(2).DataLabels.Select
Selection.Position = xlLabelPositionLeft
Selection.Font.Color = vbRed
Selection.Font.Size = 6
Selection.Font.Bold = True
.HasDataTable = False
.HasLegend = False
.HasTitle = False
.HasAxis(xlCategory) = False
.HasAxis(xlValue) = False
Set rngchart1 = Range("E5:J12")
.PlotArea.Width = rngchart1.Width
.PlotArea.Height = rngchart1.Height
.PlotArea.Top = 0
.PlotArea.Left = 0
ActiveSheet.ChartObjects(1).Activate
Set cht1 = ActiveChart.Parent


cht1.Left = rngchart1.Left
cht1.Top = rngchart1.Top
cht1.Width = rngchart1.Width
cht1.Height = rngchart1.Height
cht1.ShapeRange.ZOrder msoSendToBack
End With



Thanks