PC Review


Reply
Thread Tools Rate Thread

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

 
 
Jon Peltier
Guest
Posts: n/a
 
      11th Jul 2003
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


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to find last row with Data, avg last 30, show result on row soccerdav2003 Microsoft Excel Programming 7 12th Jun 2008 05:40 PM
Need help Finding row number of the last row with data. Fawks Microsoft Excel Programming 3 19th May 2008 05:06 AM
Copy last row from sheet 1 to last row in sheet 2 =?Utf-8?B?Q2FybGVl?= Microsoft Excel Programming 4 8th Jul 2007 02:39 AM
The best sub for last row / last column / last cell? =?Utf-8?B?anVzdG1l?= Microsoft Excel Programming 15 2nd Feb 2007 05:05 AM
Fill down to last row - copy formula to last row =?Utf-8?B?VGltVA==?= Microsoft Excel Programming 2 6th Oct 2006 04:50 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:36 AM.