I’m opening a new question to clarify as to how two or more curves can be merged in one Excel graph by using VB.NET. It was suggested by @NeilFleming1 in answering a previous question that the following code be used:
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
Set myMultiAreaRange = Union(r1, r2)
However, Range and Union in the above code cannot work and the program crashes with the error message:
'System.Data.Range' is not accessible in this context because it is 'Private'.
Probably there should be a way to make these public or there’s something else that should be done.
Not being able to solve this merging with a code I resorted to a palliative solution. I made the two columns with data to be plotted adjacent to each other and added a prior empty column. So, we have an empty column H and two columns I and J filled with the data points. Now the chartRange = ws.Range("H1..J20") renders the two curves properly overlaid. Unfortunately, the x-axis values are not the ones in column A but are just the consecutive numbers of the data points and instead of the x-axis a thick line (made of the zero points in H) appears. Don't know how to place the actual x-axis numbers correctly but that's as much as a palliative solution can do, I guess. So, how can this problem be solved using VB.NET code to automate Excel graph plotting? Thanks in advance for your help.