XValues with variable end row

[BStanton]

I am having trouble determining the best way to code the x-axis for my
chart. The beginning of the x axis will always be located in cell H2.
The end of the range will be in column H and some row which is declared
as variable "LastRow". I have tried multiple formats for declaring
this range.

I have attempted to resolve this by using multiple formats as well as
using an XY scatter plot. The y values for the multiple signals are
selected using shift end right and shift end down (from cell "J1").
This has proven to be a robust way to select all of my data for
graphing but I can't seem to get the appropriate x-axis (time scale) on
my graph.


Code:
--------------------
Range("J1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLine
--------------------


I have tried the following methods to format x axis (as well as many
others).


Code:
--------------------
ActiveChart.SeriesCollection(1).XValues = "=RunData!R2C8:R & LastRowC8"

ActiveChart.SeriesCollection(1).XValues = Worksheets("RunData").Range(Cells(2, 8), Cells(LastRow, 8))
--------------------


This is my first post and have found the archive to be quite usefull on
many occations. I love this website! Any help from the Excel masters
will be greatly appreciated!

 

[michelxld]

Hello

you may try


Dim Plage As Range
Set Plage = Worksheets("RunData").Range(Cells(2, 8), Cells(LastRow,
8))
ActiveChart.SeriesCollection(1).XValues = Plage

or

Dim Plage As Range
Set Plage = Worksheets("RunData").Range("H2:H" & LastRow)
ActiveChart.SeriesCollection(1).XValues = Plage


I hope this help you
Regards ,
michel

 

[BStanton]

No that didn't seem to work either.
Is Plage an object that needs to be defined?

 

[michelxld]

Hello

this complete example works for me ( Excel2002 ) but perhaps i didn't
understand your problem


Sub testGraph()
Dim Plage As Range, Plage2 As Range
Dim LastRow As Integer, LineSource As Integer
Dim ColumnSource As Byte

'*****XValues with variable end row in column H *****
LastRow = Worksheets("RunData").Range("H65536").End(xlUp).Row
Set Plage = Worksheets("RunData").Range(Cells(2, 8), Cells(LastRow,
8))
'*******

'*** data source : end right and shift end down (from cell "J1")******
LineSource = Worksheets("RunData").Range("J1").End(xlDown).Row
ColumnSource = Worksheets("RunData").Range("J1").End(xlToRight).Column
Set Plage2 = Range(Cells(1, 10), Cells(LineSource, ColumnSource))
'*********

Charts.Add
ActiveChart.ChartType = xlLine

With ActiveChart
..SetSourceData Source:=Plage2
..SeriesCollection(1).XValues = Plage
..Location Where:=xlLocationAsNewSheet
End With

End Sub


Regards ,
michel

 

[Peter T]

I have tried the following methods to format x axis (as well as many
others).

Code:

Worksheets("RunData").Range(Cells(2, 8), Cells(LastRow, 8))

--------------------

Your first line will always fail, the second should work providing
Worksheets("RunData") is the active sheet. If not you need to qualify the
Cells to the sheet, eg

Dim rXvals As Range

With Worksheets("Sheet2")
LastRow = .Cells(65536, 8).End(xlUp).Row
'or
LastRow = .Cells(2, 8).End(xlDown).Row

Set rXvals = Range(.Cells(2, 8), .Cells(LastRow, 8))
'or
'Set rXvals = Range(.Cells(2, 8), .Cells(65536, 8).End(xlUp))
'Set rXvals = Range(.Cells(2, 8), .Cells(2, 8).End(xlDown))
End With

With ActiveChart
..SeriesCollection(1).XValues = rXvals
End With

If you use Dynamic Names to refer to your X & Y values you will not need any
code to update your chart as data expands.

Regards,
Peter T