Dynamic selection of a range

[Sören_Marodören]

I have a VBA-program that I wants to loop through several collumns of data
and plot this data in different graphs.

When I record a macro, where I select the three collums of interest and
generate a graph, the result looks like this.

Range("A:A,D,K:K").Select
Range("K1").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData
Source:=Sheets("PEc_measdata20070907_0833").Range( _
"A1:A3501,D13501,K1:K3501"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
"PEc_measdata20070907_0833"

How can I change the code so that I in the firs turn plots column A, B and
I. The next turn A, C and J, then A, D and K, then A, E and L, then A, F and
M, and finaly A, G and N.

At the same time the lengt of data can vary. Therfore I have a variable
called No_of_Data. In the example above this variable shuld be 3501. How can
I include this in my program.

BR,
/Sören

 

[Bob Phillips]

For i = 1 To 6

sSourceData = Range(Cells(1, 1), Cells(3501, 1)).Address
sSourceData = sSourceData & "," & Range(Cells(1, i + 1), Cells(3501,
i + 1)).Address
sSourceData = sSourceData & "," & Range(Cells(1, i + 8), Cells(3501,
i + 8)).Address
Charts.Add
With ActiveChart
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData _
Source:=Sheets("PEc_measdata20070907_0833").Range( _
sSourceData), _
PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:= _
"PEc_measdata20070907_0833"
End With
Next i


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sören_Marodören]

Thanks Bob!

This was a good help for me.
I have not been using .Address before. Have to study this featur more.

Best regards,
/Sören