Variables for pie chart, split data set vba excel, using Column numbers

[Rowland Hamilton]

People,

When I code using exact ranges, letters and numbers style, I get the split data point result, with only the header row data (row 17) and the totals data (row 22):
Code:

ActiveChart.SetSourceData Source:=Range("B17:C17,B22:C22")

But, when I use numbers for the column references, It does not split the range, but rather charts the entire range B17:C22
Code:

ActiveChart.SetSourceData Source:=Range(Range(Cells(17, 2), Cells(17, 3)), Range(Cells(22, 2), Cells(22, 3)))

I actually have a code that allows for variable range columns and rows, so I don't want to be stuck with fixed column letters.

What am I doing wrong, here?

Rowland

 

[Hui]

Try:

ActiveChart.SetSourceData Source:=Range(union(Range(Cells(17, 2), Cells(17, 3)), Range(Cells(22, 2), Cells(22, 3))))

 

[NARAYANK991]

Hi ,

It shows that Excel is smarter than we give it credit for !

Since you have shown two possibilities which gave you different results , let me post two more ; once you have tried these you will understand how Excel looks at these.

1. Range("B17:C17","B22:C22")

2. Range(Range(Cells(17, 2), Cells(17, 3)).Address & "," & Range(Cells(22, 2), Cells(22, 3)).Address)

Hint : Try affixing .Address to all four variations , and see what the Immediate window displays e.g.

?Range("B17:C17, B22:C22").Address

Narayan

 

[Rowland Hamilton]

Thanks, Hui. Replacing the first range with union solved the problem.

Thanks, Narayank991, I tried #2

Range(Range(Cells(17, 2), Cells(17, 3)).Address & "," & Range(Cells(22, 2), Cells(22, 3)).Address)

but it wouldn't accept the syntax.

-Rowland

 

[NARAYANK991]

Hi ,

I tried it and it worked.

Narayan

 

[Hui]

Narayan,
I learned Union from you

 

[NARAYANK991]

Hi Hui ,

Thanks , but I think I learnt it from Luke !

My point in posting what I did was not to contradict your solution , but to inform the OP the reason why the two methods were giving different results.

In one case , the entire range address is in the form of a string ; as in the case of INDIRECT , Excel does not try to interpret the string except as an address.

In the other case , each of the components is no longer a string but a range ; here Excel decides it can do some optimization , which is why it combines all of the cells into a composite range.

This can be inferred if we try the other two cases I posted :

Range("B17:C17","B22:C22")

is two separate addresses , and Excel decides it can optimize ; the result is a composite range.

Range(Range(Cells(17, 2), Cells(17, 3)).Address & "," & Range(Cells(22, 2), Cells(22, 3)).Address)

is treated differently , because here the highlighted portion is a text string , and Excel treats it as such.

Union is now very much a part of my VBA , especially in event procedures , where we can use it within Application.Intersect

Narayan