how to create vertical line in excel for multiple charts/multiple y ranges

**mimishell** · 10-13-2014, 07:57 PM

I am trying to add in vertical lines to my line charts to represent an important data/event, and the way I went about doing so was adding in the max value of the y range for the specific date as seen below and making it into a bar chart.

But the problem is I have over 22 charts that I want to have these vertical lines on, and all of them have different y ranges.

What ends up happening is the lines look either too short or in the case in the bottom chart, go off range to make the data look too small/unviewable.

Does anyone know any other solutions for adding in vertical lines like seen below, but a solution that doesn't require me to make a unique vertical line for each chart?vertical date lines_offscale.jpg

**JBeaucaire** · 10-13-2014, 08:25 PM

Whatever methodology you are using to determine the value of 1,400,000 should be amended to set the value equal to the next multiple of 200k above the maximum value in your key data column.

This formula does that in a cell: =CEILING(MAX(A:A), 200000)

Now use that approach in VBA to detect the maximum value in your key data column, then move it up to the next 200k. That should keep your data from being smashed when the lines are later added.

**mimishell** · 10-16-2014, 01:22 PM

Hi JBeaucaire,

How would I apply that solution to multiple charts (22) if the y axis has different ranges? Wouldn't I have to use a unique "max ceiling" forumla for each chart? I am looking for a solution that can be applied to all the charts at once if possible.

Best,
Michelle

**JBeaucaire** · 10-16-2014, 02:01 PM

Yes, the code you create to apply to your charts would need to find the MAX value in that key column and adjust it using the CEILING approach. That can be done in VBA.