Changing a range number to a variable

  • Thread starter Thread starter Dave Baker
  • Start date Start date
D

Dave Baker

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:M1540"), PlotBy:=xlColumns
Click to expand...

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
Click to expand...

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
 
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 said:
I have the following line created in a macro that creates a new chart from my
table every day:
Click to expand...
Source:=Sheets("tbCurrency").Range("B1:B1540,M1:M1540"), 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:
Click to expand...
LastCol & ",M1:M" & LastCol), PlotBy:=xlColumns
 
Back
Top