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

Discussion in 'Microsoft Excel Charting' started by Jon Peltier, Jul 11, 2003.

  1. Jon Peltier

    Jon Peltier Guest

    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
     
    Jon Peltier, Jul 11, 2003
    #1
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    finding the value of a point on chart

    Guest, Apr 19, 2005, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    161
    Guest
    Apr 20, 2005
  2. Big H

    Bar Chart To Line Chart for last 3 points only

    Big H, Oct 13, 2005, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    219
    Guest
    Oct 13, 2005
  3. Guest

    finding the password to unlock a protected cell/chart

    Guest, Jun 13, 2006, in forum: Microsoft Excel Charting
    Replies:
    3
    Views:
    1,331
    Andy Pope
    Jun 13, 2006
  4. Rick

    Can a chart series (row-based) occupy more than one row

    Rick, Oct 3, 2009, in forum: Microsoft Excel Charting
    Replies:
    2
    Views:
    216
  5. Al Charbonneau

    Finding second data point on primary axis of two axis chart

    Al Charbonneau, Dec 9, 2009, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    380
    Mike Middleton
    Dec 9, 2009
Loading...

Share This Page