Setting Chart to PlotBy xlRows before selecting range larger than 255

[JimDandy]

I have a charting problem with some VBA code where the problem is the way that Excel assumes every chart created has its data arranged in Columns instead of Rows. As a result, the charts I need to create all end up to have too many data points to work and I get an error telling me that the chart can only have 255 data points. However, if there was an option to select the data in such a way that Excel understood that the data needs to be swapped columns to rows before producing the error then my charting needs would work.

Attached is a spreadsheet that has data from Columns G through NJ that needs to be plotted (there is data on rows 6 and 7 along with rows 65 and 66). There is a macro I recorded to allow the creation and set up of the chart how I need it. When creating the Macro I used a smaller amount data so to not invoke the error and I was able to swap the rows and columns in the Select Data dialog, and this function appears in the VBA code as PlotBy:=xlRows. It was my hope that I could get in front of the error with VBA and set the PlotBy before selecting the full set of data but I cannot seem to find the right VBA code and chart creation methods to achieve this.

Is there some other way to create this chart using VBA and get around the error? I have over 60 charts I need to automation their creation so it needs to be in VBA.

I've tried a number of different code styles from many different sources to create the chart but they all seem not to have large enough data sets to run into this issue.

[Andy Pope]

Simply move the activecell out of the data, so no data is preloaded into the chart.
See new line to add to your existing code.

Please Login or Register  to view this content.

[JimDandy]

Thank you....I too noticed earlier that the active cell was causing the example spreadsheet to give a false error about the 255 limit whenever I had selected a cell within the data range and I did something similar with just Range("A1").Select as the first line in my code however if you were to change the end of the ranges from JB7 or JB66 to the actual end of the data range which is NJ6 and NJ66 you will see the actual issue I'm having.

I sent the example set in the JB range to show that the overall code was working. I just can't get it to work with the complete range I need out to column NJ.

[Andy Pope]

The following works for me.

Please Login or Register  to view this content.

[JimDandy]

Superb!

Thank you, this is very much appreciated.