VBA in Excel: Controlling a Chart with Dynamic Ranges

Question

I have been working on an excel model with dynamic charts. Essentially a user should be able to change the periods charted on a diagram - so fairly simple. However, I keep having issues with making the VBA work as I intend (I do not get errors, but it does not behave as I would expect). What I am attempting to do:

Set a dynamic legend (depending on how many items the user wants to chart) - the number of items is defined in Excel and is simply added to the number of rows in VBA.
Set a dynamic range (start and end depends on user input), however, it seems like I cannot define where the range containing data for plotting starts.

In my code below Excel seems to ignore the parameters in Rng2, i.e. it starts plotting from column 53 until column 77, instead of what I intend being from column 77 to 89. Alternatively if I replace the content of Rng3 with hardcoded references it works just fine (i.e. Rng3 = .Range("$AZ5662:$AZ5667,$BY$5662:$CJ$5667")).

What is wrong?

Thank you very much!

Best regards, Philip

Set Cht = .ChartObjects("Chart 14").Chart
Set Rng1 = .Range(Cells(5662, 52), Cells(5663 + Worksheets("Control").Range("F20"), 52))
Set Rng2 = .Range(Cells(5662, 77), Cells(5663 + Worksheets("Control").Range("F20"), 88))
Set Rng3 = .Range(Rng1, Rng2)
Cht.SetSourceData _
Source:=Rng3
Cht.PlotBy = xlRows

newacc2240 · Accepted Answer · 2018-09-15 13:27:50Z

1

When you use Range(Range1,Range2), it fetches the upper-left cell of Range1, and the lower-right cell of Range2 to build a range.

Therefore the result of your Rng3 is a square starts from Cells(5662,52) and ends at Cells(5663+X,88), which is not you wanted.

You can try another method called Union( Rng1 , Rng2 , .... )

It will store all the boundaries of each Range object given, so it can be several separated areas, or even an odd shape such as a heart or something :P

answered Sep 15, 2018 at 13:27

newacc2240

1,4351 gold badge9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Philip Over a year ago

Thank you, using Union(rng1,rng2) solved the problem! :)

Collectives™ on Stack Overflow

VBA in Excel: Controlling a Chart with Dynamic Ranges

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related