Chart with variable sourcedata

smund

Board Regular
Joined
Mar 15, 2005
Messages
52
HI,
How can I do a macro thats create a chart with sourcedata is not a fixed range.Could I define the first and last cell of the range?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Here's a thought to start you off - how about defining a "floating" range which would be re-defined each time the macro is run as long as the range always starts in the same spot?

For example, my range contains the following information:

Cell A3 = "Month"
Cell A4 to A7 = January to April/06
Cell B3 = "Value"
Cell B4 to B7 = 14 , 13, 18, 12
Cell C1 = "Rows"
Cell C2 = "Columns"
Cell D1 and D2 - see formulas in step 1 below


1. Create 2 cells which count the number of rows and columns in the range.
In cell D1, enter the formula =COUNTA(A4:A65536)
In cell D2, enter the formula =COUNTA(4:4)

Name the range D1 as "Rows" and D2 as "Cols"

Note: If you always have the same number of columns, you don't need to define a range or formula in D2...see the offset formula below.

2. Set up your starting point reference
In the above case, the range will always start right below the heading "Month", so I'll use that as my standard reference. I will name range A3 as "Start"

3. Set up a floating range
Using Insert / Name / Define, set up the following range characteristic
Names in workbook: Float
Refers to: =Offset(Start,1,0,Rows,Cols)
Then hit the Add button.

The criteria for this formula are as follows:
=OFFSET(reference, rows, cols, height, width),
so Excel goes to the "Start" cell, goes down 1 row, across 0 columns, and selects the range starting at that intersection and continuing on for "Rows" down and "Cols" across.

Note: If you have a set number of columns, as described in step 1 above, you could use the formula =Offset(Start,1,0,Rows,2) where 2 is the standard number of columns.


This should help you achieve a changeable range size. I'm assuming that you will re-run the macro each time you want to generate the chart - this lets you use a reference to this "Float" range in the macro and have it always refer to the current version of this range when creating the new chart

FYI, if you are just "recording" the macro as you create a chart, you will need to go into the Tools / Macro / Visual Basic Editor and change the range that it has set during recording (i.e.,
Code:
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3:A45"), PlotBy  :=xlColumns
Should be changed to
Code:
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("Float"), PlotBy :=xlColumns
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top