Changing Range (2003)

Topic

New Reply

I’d like to know how to change a range using vba. When I’ve wanted to change something (like this) before, I’ve just concatenated a variable and placed it between the parentheses – but it does not work with Range.

I want this, as the result.
ActiveChart.SetSourceData Source:=Sheets(“Data”).Range(“a2:a40,d2:d40″), _
PlotBy:=xlColumns

I’m trying to achieve it, like this
Dim a as integer, b as integer, c as string, d as string, e as string
a=40
b=40
c='”a2:a”
d=”,d2:d”‘
e=concatenate(a & c & d & 4)
ActiveChart.SetSourceData Source:=Sheets(“Data”).Range(e), _
PlotBy:=xlColumns

Reply | Quote

Replies

Try this:

c= “a2:a”
delete the apostrophe, before the sequence
and
e = c & a & d & b

Reply | Quote

Thanks, Servando. I though I’d tried every sort of configuration. Ah, guess I missed that one or maybe I didn’t try without Concatenate. Also, I keyed in the wrong sequence for ‘e’ on my post. Thanks again, John

Reply | Quote

Rather than concatenating strings to get ranges, you could use the Cells property and the two property version of Range

Dim lastRowNumber as Long
Dim myRange as Range
lastRowNumber = 40
With Sheets("Data")
Set myRange = Range(.Cells(2,1), .Cells(lastRowNumber,1))
End With
MsgBox myRange.Address: Rem A2:A40

To get a discontinous range, you could use Application.Union

'as above
Set myRange = Application.Union(myRange, myRange.Offset(0,3))

ActiveChart.SetSourceData Source:=myRange, PlotBy:=xlColumns


Reply | Quote

Thanks, Mike – very interesting. John

Reply | Quote