Selecting a range of columns based on the last column

Post your current code to find the last column.

Changed your function to this.

Please Login or Register  to view this content.

In case lastcolumis AD it gives you

Please Login or Register  to view this content.

In your Port code I changed

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Which gives you firstlttr = "T" and lttr = "AD"
Now you can play around with both variables to suit your need.

What I see is -- rather than pulling the data the chart(s) need into a nice, contiguous range that the chart can easily reference -- you are setting each chart's source data to a complex, multi-area range. As one who never uses VBA for charting, I would probably have done something that pulled the necessary data into a contiguous range, then used dynamic named ranges to build the chart data series. But, if you are happy with this, and it is working for you, then we should be able to work with this.

It appears that the charts exist before the procedure is run, which also suggests that, if we get it working right, each chart can have multiple data series in it (before the code is run). It should be possible to do this in a way that will not completely redo each data series.

In my experience, when you use the .SetSourceData method to change a chart's data series, this can cause Excel to completely re-evaluate all of the data series in the chart. In most charts (that I see) where you have a second very different data series for the horizontal line (often using a combination chart), using the .SetSourceData method can undo anything you have done to create these effects. Is there an external requirement to use the ,SetSourceData method?

I would expect to edit each data series individually. Maybe something like:

Please Login or Register  to view this content.

If A) the chart exists before the code runs and B) the necessary data series for the horizontal line also exists and C) the ranges where the max/min/horizontal line data does not change, then this small change to this part of the code should allow you to change the main data series without changing the horizontal lines. You would then be able to add the horizontal lines to the chart (however you did that before this part of the project) and they will stay in the chart.

If you are required to use .SetSourceData or there is something else that I am missing, then I could see modifying this code to add/edit the horizontal line data series after setting the source data to get the horizontal line in the chart. But it ultimately still boils down to the same thing -- retaining or adding or editing a second horizontal line series in each chart. As noted, I don't use VBA to do my charting, so this is not the kind of VBA coding I am good at (I would tend to find ways to do this that don't use VBA). If you will help us understand exactly what the charts look like before this code is run and exactly how you want to change the chart, those here who are good with VBA will be able to help come up with something that will edit the data series and still retain or add back in the horizontal lines.

I never, ever use charts so this is a shot in the dark but what if you change this too.

Please Login or Register  to view this content.

just like you did with rfrnce.