CODEWITHSUNDEEP
excelvbadynamicchartsrange
Philip
Philip

Reputation: 15

VBA in Excel: Controlling a Chart with Dynamic Ranges

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:

  1. 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.
  2. 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

Upvotes: 0

Views: 153

Answers (1)

newacc2240
newacc2240

Reputation: 1425

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

Upvotes: 1

Related Questions