# [Solved] Range within a range

#### BrianDP1977

##### Board Regular
This will probably be easiest if I break my question into three parts.

1) Is it possible to pass a chart named ranges (I’m going to assume yes but have not yet tried it because I want to get the other two questions asked)?

2) In charts (a stacked bar chart to be specific), is there a way to define two separate ranges of information to be displayed for a single series. For example, the value for a series would typically be defined by the range \$H\$4:\$H\$28. Is it possible to define it so that it includes separate ranges such as \$H\$4:\$H\$5\$ & \$H\$10:\$H\$11 & \$H\$20:\$H\$21?

3) If all the above is true (primarily the 2nd one), how would one go about defining a range within a range. As evidenced by the 2nd question, I currently have a large series of data in a column defined from H4 to H28 (named Data_All). This column of data is further subdivided into three other sections. Let’s say H4:H9 (named Data_1) is one section, H10:H19 (named Data_2) is another, and H20:28 (named Data_3) is the final one. The current chart displays all the data values from H4 to H28. I would like to create code that, when a button is toggled on, it will change from the chart showing all the data to one showing truncated data (is that the right word?) which displays the first two cells in each of the three sections of the entire data range (the resultant example of \$H\$4:\$H\$5\$ & \$H\$10:\$H\$11 & \$H\$20:\$H\$21). The section sizes may change over time so the code would have to use dynamic names to search through specified ranges to determine the first two cells in the specified range. Is this possible?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### BrianDP1977

##### Board Regular
Thanks for the info. I haven’t had a chance to look at everything on the site but I’ve thought of something. Actually, from sitting back and thinking about it, I actually don't need to necessarily format the series range entry with non-contiguous data (i.e. the 2nd question). All I need to do is:

1) Figure out a way to define a new range which is defined as the 1st two rows of each of the original three sections. A less robust way to do this would be to output only the first two rows of each section (Data_1, 2, 3) to a new range of cells (Data_First_2_rows).

2) Then, upon a button press, change the series reference from the original data range (Data_All) to this new range (Data_First_2_rows). I’d rather just be able to define a range within a range (i.e. Set Data_First_2_rows equal to the first two rows of the sections Data_1, Data_2, Data_3 within Data_All (this last reference to Data_All is probably unnecessary since Data_1,2,3 are already defined)) and have this newly stored range called by the button to take the place of Data_All. However, I do not know how to figure out how to define this. Thank you for the help.

#### BrianDP1977

##### Board Regular
Got it worked out. Thanks.

Replies
0
Views
296
Replies
7
Views
441
Replies
0
Views
149
Replies
1
Views
954
Replies
0
Views
1K

1,195,849
Messages
6,011,953
Members
441,657
Latest member
Diupsy

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