Re: Finding the last row of chart....

Ant -

Check out

xlsChart.Parent.BottomRightCell

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Ant wrote:
> Hi All,
>
> I'm creating many worsheets all with 15-20 charts of type
> xl3DColumnClustered. I need to plot the source data of each chart,
> below each of these charts. Hence I need to know the row at which a
> chart ends.
>
> Can someone help me out, to find out the row where the chart ends. I
> use a procedure to add the chart which is as follows.
>
> While creating the charts, each chart is positioned based of the
> argument chtTop which I increment by 400 for each call of this
> procedure. Whats is this 400 actually, from this can we find out where
> the chart ends or where the next chart begins?
>
>
> Public Sub addChart(strChartXVal As String, strChartYVal As String,
> ByVal strChartTitle As String, strChartXTitle As String,
> strChartYTitle As String, ByVal strSheet As String, ByVal chtTop As
> Long, chtNo As Integer, Optional strChartY1Val As String, Optional
> strLabelYVal As String, Optional strLabelY1Val As String)
> With xlsapp.Application
> Dim xlsChart As Excel.Chart
> .Sheets(strSheet).Select
> Set xlsChart = .ActiveSheet.ChartObjects.Add(0, chtTop, 450,
> 250).Chart
> xlsChart.ChartType = xl3DColumnClustered 'xlColumnClustered
> If chtNo = 1 Then
> xlsChart.SeriesCollection.NewSeries
> xlsChart.SeriesCollection(1).XValues = "=(" & strChartXVal
> & ")"
> xlsChart.SeriesCollection(1).Values = "=(" & strChartYVal
> & ")"
> xlsChart.HasLegend = False
> xlsChart.ApplyDataLabels 2, False
> xlsChart.SeriesCollection(1).DataLabels.Font.Name =
> "Arial"
> xlsChart.SeriesCollection(1).DataLabels.Font.FontStyle =
> "Regular"
> '040703
> xlsChart.SeriesCollection(1).DataLabels.Font.Size = 8
> xlsChart.SeriesCollection(1).DataLabels.Font.ColorIndex =
> 2
> xlsChart.SeriesCollection(1).DataLabels.Font.Background =
> 2 ' xlTransparent
> '030703
> If strChartY1Val = "D" Then
> xlsChart.SeriesCollection(1).Points(xlsChart.SeriesCollection(1).Points.Count
> - 1).Interior.ColorIndex = 36
> xlsChart.SeriesCollection(1).Points(xlsChart.SeriesCollection(1).Points.Count).Interior.ColorIndex
> = 6
> End If
> ElseIf chtNo = 2 Then
> xlsChart.SeriesCollection.NewSeries
> xlsChart.SeriesCollection.NewSeries
> xlsChart.SeriesCollection(1).XValues = "=(" & strChartXVal
> & ")"
> xlsChart.SeriesCollection(1).Values = "=(" & strChartYVal
> & ")"
> xlsChart.SeriesCollection(1).Name = "=" & strLabelYVal
>
> xlsChart.ApplyDataLabels 2, False
> xlsChart.SeriesCollection(1).DataLabels.Font.Name =
> "Arial"
> xlsChart.SeriesCollection(1).DataLabels.Font.FontStyle =
> "Regular"
> '040703
> xlsChart.SeriesCollection(1).DataLabels.Font.Size = 8
> xlsChart.SeriesCollection(1).DataLabels.Font.ColorIndex =
> 2
> xlsChart.SeriesCollection(1).DataLabels.Font.Background =
> 2 ' xlTransparent
> xlsChart.SeriesCollection(2).Values = "=(" & strChartY1Val
> & ")"
> xlsChart.SeriesCollection(2).Name = "=" & strLabelY1Val
> xlsChart.SeriesCollection(2).DataLabels.Font.Name =
> "Arial"
> xlsChart.SeriesCollection(2).DataLabels.Font.FontStyle =
> "Regular"
> '040703
> xlsChart.SeriesCollection(2).DataLabels.Font.Size = 8
> xlsChart.SeriesCollection(2).DataLabels.Font.ColorIndex =
> 2
> xlsChart.SeriesCollection(2).DataLabels.Font.Background =
> 2 ' xlTransparent
> xlsChart.HasLegend = True
> xlsChart.Legend.Position = -4107 'xlLegendPositionBottom
> End If
>
> With xlsChart
> .HasTitle = True
> .ChartTitle.Characters.Text = strChartTitle
> .Axes(1).HasTitle = True
> .Axes(1).AxisTitle.Characters.Text = strChartXTitle
> .Axes(1).TickLabels.Font.Name = "Arial"
> .Axes(1).TickLabels.Font.FontStyle = "Regular"
> .Axes(1).TickLabels.Font.Size = 8
> .Axes(1).TickLabels.Alignment = -4108 'xlCenter
> .Axes(1).TickLabels.Orientation = -4171 'xlUpward
> .Axes(1).HasMajorGridlines = False
> .Axes(1).HasMinorGridlines = False
> '040703
> .Axes(1).TickLabelSpacing = 1
> .Axes(1).TickMarkSpacing = 1
>
> .Axes(2).HasTitle = True
> .Axes(2).AxisTitle.Characters.Text = strChartYTitle
> .Axes(2).AxisTitle.Font.Name = "Arial"
> .Axes(2).AxisTitle.Font.FontStyle = "Regular"
> .Axes(2).AxisTitle.Font.Size = 7
> .Axes(2).AxisTitle.HorizontalAlignment = -4108 'xlCenter
> .Axes(2).AxisTitle.VerticalAlignment = -4108 'xlCenter
> .Axes(2).AxisTitle.Orientation = -4128 'xlHorizontal
> .Axes(2).AxisTitle.Left = 45
> .Axes(2).MinimumScale = 0
> .Axes(2).MaximumScale = 10
> .Axes(2).MinorUnit = 1.666666666
> .Axes(2).MajorUnit = 5
> .Axes(2).Crosses = -4105 'xlAutomatic
> .Axes(2).ReversePlotOrder = False
> .Axes(2).ScaleType = -4132 'xlLinear
> .Axes(2).DisplayUnit = -4142 'xlNone
> .Axes(2).HasMajorGridlines = True
> .Axes(2).HasMinorGridlines = True
> '''''''''''''''''''''''''''
> '040703
> .Elevation = 15
> .Perspective = 30
> .Rotation = 20
> .RightAngleAxes = True
> .HeightPercent = 100
> .AutoScaling = True
> .PlotArea.Top = 22
> .PlotArea.Width = .ChartArea.Width
> .PlotArea.Height = .ChartArea.Height - 45
> End With
> End With
> End Sub
>
>
> Thanks in advance.
>
> Ant