Changing a range number to a variable
 

I have the following line created in a macro that creates a new chart from my
table every day:

ActiveChart.SetSourceData Source:=Sheets("tbCurrency").Range("B1:B1540,M1:M1 540"), PlotBy:=xlColumns

However, each day I am adding 1 more row to my table, so I want my chart to
reflect the number of rows in the table - today it is 1540 rows, but it will
be 1541 rows tomorrow.

I have created a variable that calculates the last filled row in the table,
with the line below:

LastCol = Worksheets("tbCurrency").Range("A65536").End(xlUp) .Row

The only thing I can't figure out now is how to replace the 1540 with the
LastCol variable! I've tried quite a few permutations, but I can't get it
right. For example, things like this:

ActiveChart.SetSourceData Source:=Sheets("tbCurrency").Range("B1:B" & LastCol & ",M1:M" & LastCol), PlotBy:=xlColumns

but that didn't work. I'm sure it's really easy, but after trying all the
ways I can think of, and spending half a day on search engines, I'm willing
to let someone else show me how easy it is! :-)

Dave

Changing a range number to a variable
 

Dave,

please see my reply to your earlier post ("dynamic range to ..."). I would
use a different approach - use named ranges to update your chart, and update
ranges whenever you want in visual basic. It's simple and works fine for me
for years.

RADO


"Dave Baker" wrote in message
...

I have the following line created in a macro that creates a new chart from
my
table every day:

ActiveChart.SetSourceData
Source:=Sheets("tbCurrency").Range("B1:B1540,M1:M1 540"), PlotBy:=xlColumns

However, each day I am adding 1 more row to my table, so I want my chart
to
reflect the number of rows in the table - today it is 1540 rows, but it
will
be 1541 rows tomorrow.

I have created a variable that calculates the last filled row in the
table,
with the line below:

LastCol = Worksheets("tbCurrency").Range("A65536").End(xlUp) .Row

The only thing I can't figure out now is how to replace the 1540 with the
LastCol variable! I've tried quite a few permutations, but I can't get it
right. For example, things like this:

ActiveChart.SetSourceData Source:=Sheets("tbCurrency").Range("B1:B" &
LastCol & ",M1:M" & LastCol), PlotBy:=xlColumns

but that didn't work. I'm sure it's really easy, but after trying all the
ways I can think of, and spending half a day on search engines, I'm
willing
to let someone else show me how easy it is! :-)

Dave

Changing a range number to a variable
 

On Sat, 01 Nov 2003 13:44:30 +0800, Dave Baker wrote:

ActiveChart.SetSourceData Source:=Sheets("tbCurrency").Range("B1:B" & LastCol & ",M1:M" & LastCol), PlotBy:=xlColumns

but that didn't work.

Strange - it does now! Sorry about all of that. Gremlins sneaking into my
code somewhere.

Dave