1

The structure of my document can be seen from the image below. I wish to build a timeline chart from columns A (excluding cell A1, it is crucial for the chart that this particular cell is not included), B and D. I am looking for ways to make the chart dynamic by using VBA so that new events added by the user will be automatically shown in the chart without having to manually change the range of the chart.

The range doesn't have to change instantly/continuously -- as long as it changes when the macro behind the button "Create a New Event View" is clicked. When the user has more events to add to the table, she can just add the events into the table and by creating a new table and deleting the old one she will have an updated chart as well.

The question is, how to pull this off?

enter image description here

Improve this question

2 Answers 2

Reset to default
3

You don't need VBA to make the chart dynamic. Just create dynamic named ranges that grow and shrink with the data. Your VBA for the chart can refer to that named range, without adding burden to the code. But you may not even need VBA at all. The chart defined with dynamic ranges will update instantaneously. No code required.

Range name and formula for chart labels:

chtLabels =Events!A2:Index(Events!$A:$A,counta(Events!$A:$A))

Range name and formula for column B

chtBegin =offset(chtlabels,0,1)

Range and formula for column D

chtDuration =offest(chtlabels,0,3)

Edit the data source and instead of the fixed cell ranges, enter the named ranges in the format

=Events!*RangeName*

You need to supply the respective range name to the series values and the chart's category axis.

Note: when you supply dynamic range names as the source for a chart, you MUST include either the file name or the sheet name with the range name reference. When you close and re-open the dialog, you will find that Excel automatically converts your entry to the format [Filename]RangeName

Note 2: there are many different formula options to create dynamic range names. In this case, we're using and index of column A and determine the last populated cell by counting the cells. This only works if all cells in column A have text. If your data has gaps in column A (which I don't think you do), different formulas can be applied to determine the range.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you very much, your answer was of tremendous help! I read your advice carefully and tried to implement it into the chart. However, I ran into a problem when I was modifying the data source. Excel gave me an error saying: "the maximum number of data series per chart is 255". I double-checked and as far as I know I did everything by the book. Is there a way to stop this formula from going beyond point 255 so that the error message would not appear?
Could you possibly supply a sample file with your data structure and dummy data on one of the many file sharing sites? I'm not quite sure where the problem is. The suggestion I gave will add one series at a time, so maxing out the series limit requires some determination. I'd like to see what you've done.
1
  1. Convert the range to a table.
  2. Select Any cell in the range.
  3. Then press Ctrl+L. This will transform your range into a Table

You can then format it and right click on it and insert Chart. This will allow you to add rows or columns. Even add formula columns that will automatically fill down for you also. You also can now set up formulas That use dynamic ranges that refer to this row, call a column by it's name. Use the headers/Data. Get Subtotals. It will allow a lot of flexibility.

Improve this answer

1 Comment

Thank you user2140261 for bringing forth this excellent method. It is of great value in many circumstances. However, in this case I believe that manually setting the range is better, such as the method in teylyn's answer. I wish to implement the dynamic range in the VBA code that runs behind the button so every time a user creates a new chart it will have a dynamic range.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.