How to define range with variable

[tsx2004]

Hi,

I'm trying to automate a chart plot, but I'm stuck on how to define a dynamic range.

ActiveChart.SetSourceData Source:=Sheets("ByFabLot%").Range("A4:A58, D4:D58"), _
PlotBy:=xlColumns

Instead of a static range, the A58 and D58 will be a variable and will be expanded.

I was trying to put in "A4: Xrow, D4: Yrow" as my variable, but i get an errow. Any suggestion? Thanks in advance

 

[Norie]

Try this, assuming XRow is your variable.

 ActiveChart.SetSourceData Source:=Sheets("ByFabLot%").Range("A4:A" & XRow & ", D4:D" & XRow), _
PlotBy:=xlColumns

 

[tsx2004]

It works well. Thanks a lot. Do you mind to tell me what are the & for? Thanks

 

[Norie]

The ampersands (&) are used in VBA to concatenate (join) strings.

You can also use them in worksheet formulas.

By the way this isn't the only way to acheive what you want.

 

[tsx2004]

Thanks for the information.

What is another way? So, i can have another tool for future reference.

 

[Norie]

Well you could try something like this, but I probably wouldn't recommend it.

Dim rng As Range

    With Sheets("ByFabLot%")
        Set rng = Union(.Range("A4").Resize(XRow - 3), .Range("D4").Resize(XRow - 3))
    End With
 
    ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns