How to code a Variable Range in VBA

[varts]

I'm new to VBA so I hope someone can help me with a Range question.


- I'm using Excel 2003

- Every week I add several days/rows of data

- I have a Chart that I want to update when I add new data

- I would like to pass the number of data points to be charted to the Sub

- I created a user defined function that returns the last row that contains data

LastRowData = CountRows(1)

- Here is how is might go:


Sub UpdateChart(DataPoints)


Sheets("Charts").Select

ActiveSheet.ChartObjects("Chart 2").Active

ActiveChart.ChartArea.Select


LastRowData = CountRows(1)


'Xvalues are in ColB; Values are in ColC


??This is where I do not know how to define ranges??


ActiveChart.SeriesCollection(1).XValues = "='Letter Data L'!??:??"

ActiveChart.SeriesCollection(1).Values = "='Letter Data L'!??:??"


End Sub


Any Help is appreciated - Many Thanks!

 

[Hui]

Varts


Are you trying to do this in VBA because the data area for the charts is changing?


You can have the charts update automatically using techniques like those shown in:

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/


or try the following


LastRowData = CountRows(1)


XRange = "='Letter Data L'!B1:B" + Trim(Str(LastRowData))

ActiveChart.SeriesCollection(1).XValues = XRange


VRange = "='Letter Data L'!C1:C" + Trim(Str(LastRowData))

ActiveChart.SeriesCollection(1).XValues = VRange

 

[varts]

Thanks Hui,


Yes, the data area is growing with each additional row of data.


A variable called DataPts tells me how many days of data is to be charted.


Here is what I came up with while I was waiting for someone to respond to my post:


FirstRow = 8

Delta = LastRow - DataPts + 1


If Delta >= DataPts Then FirstRow = Delta


ActiveSheet.ChartObjects("Chart 2").Activate

ActiveChart.ChartArea.Select


Set rngXValues = Worksheets("Letter Data L").Range("B" & FirstRow & ":B" & LastRow)

Set rngValues = Worksheets("Letter Data L").Range("S" & FirstRow & ":S" & LastRow)


ActiveChart.SeriesCollection(1).XValues = rngXValues

ActiveChart.SeriesCollection(1).Values = rngValues