Change $A$1:$C$6500 to current region of A1 instead

[VBAProIWish]

Hello All,

I have one this one line of code here and would like to change it to the current region of cell A1 instead of only going to 6500. The # of rows will get longer each month.

    ActiveChart.SetSourceData Source:=Range("GRAPES![B][SIZE=3][COLOR=red]$A$1:$C$6500[/COLOR][/SIZE][/B]")

I know I can just put in "$A$1:$C$1000000" but don't want to select unnecessary rows if I don't have to.


Thanks much

 

[Norie]

You could try this.

Worksheets("GRAPES").Range("A1").CurrentRegion

 

[VoG]

Maybe this (untested)

ActiveChart.SetSourceData Source:=Sheets("GRAPES").Range("A1").CurrentRegion

 

[VBAProIWish]

Both worked EXCEPT,

I forgot that some of my Worksheet names has more than one word with a space in between them.

For example, one of my worksheet names is "Cht-P, Park"...

so...I remembered that when there is a worksheet with spaces in the name and it has to be referred to, it must include single quotations in between the double quotations.

So, I tried this here...

Worksheets("'Cht-P, Park'").Range("A1").CurrentRegion

and this...

ActiveChart.SetSourceData Source:=Sheets("'Cht-P, Park'").Range("A1").CurrentRegion

With the single quotations and neither one worked.

Just when I thought I had worksheet names with spaces in code mastered

Any idea what's going on here?

 

[Norie]

Yes, you don't need single quotes.

 

[VBAProIWish]

Ahhh! Thank you!

So, why do you sometimes need the single quotes and sometimes not?

 

[VoG]

You need the single quotes in worksheet formulas, not VBA.

 

[VBAProIWish]

Well,

Thank you both for the help!

My new VBA "learn" for the day!

​