I need to plot two sets of values against one another. The values are located in various uncontiguous cells. There are a very large number of cells.
I tried using the Union() function to create the two ranges of cells that I could then assign to .Xvalues and .Values. This method supports creating very large combinations of cells, however, after unioning the cells, Excel no longer remembers the order in which I added cells to the range so when I run the plot, the lines do not connect in the correct order from point to point. Most of my data is organized in rows, but Excel seems to pick data in order by column first.
I also tried creating strings like this: "Sheet1!$A1:$A1,Sheet1$B5:$B5, ..." and assigning those to .Xvalues and .Values. This method respects the order of the cells so the lines plot as expected, but it generates an error when the string gets around 8000 characters so it won't work for large data sets.
Is there some other way to assign a large number of uncontiguous cells to .Xvalues and .Values for plotting?
I am running Excel 2010 on Windows 7.