Range Statement in chart

[Guest]

Hi.

I'm having trouble referring to the column when creating a chart. The
following statement will work, but the "W170" will actually be variable and I
don't know how to refer to the last column letter in the range statement. I
do have a variable called "lastColumn" that givese me the number of the last
column, but I don't know how to find the letter for the range statement. Can
someone tell me how to do this?

ActiveChart.SetSourceData Source:=Sheets("By Hour").Range("A1:W170"), PlotBy _
:=xlColumns

 

[Gary Keramidas]

maybe this

Range(Cells(1, "A"), Cells(170, lastcolumn))

 

[Guest]

You might try this . . . By using CurrentRegion your data range will
automatically expand and contract as data is added to removed. Then you
could do away with the "lastcolumn" variable.

ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("A1").CurrentRegion, PlotBy:=xlColumns