# Chart with variable sourcedata

#### smund

##### Board Regular
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 the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### mktrapnell

##### New Member
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)

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``

Replies
5
Views
107
Replies
5
Views
88
Replies
1
Views
522
Replies
0
Views
630
Replies
0
Views
86

1,136,431
Messages
5,675,806
Members
419,586
Latest member
RoteichA

### 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.

### Which adblocker are you using?

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

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