# Graphs from two non-adjacent cells

#### MisterV

##### New Member
First of all, just wanted to say thanks for all the useful info on here. I lurked, like most people, getting the solutions from other people's threads. You guys save people a lot of time, which I think is awesome.

Now, to my problem, which I haven't been able to solve despite a couple hours' searching and tinkering. I came close, but in other ways, not really, and it's bound to be something simple. I'm only a noob who goes around scavenging other people's code, please bear with me.

Anyway, let's say I've got a column of data, about 600 cells. I need to make multiple small graphs based on two non-adjacent cells in that same column. The data comes from individuals at certain time points, each individual has about twenty timepoints, so I need 30 lines to appear on the same graph to see whether they are going up or down, and to exclude those graphs aren't doing what they're supposed to, check for outliers, etc.

 A B Time Data 1 40 2 35 3 38 4 30 5 39 6 45 7 47 etc

<tbody>
</tbody>
The problem is, while creating those multiple series with a .SeriesCollection loop was successful, it takes a range of cells and not two cells separately.
I would need graphs of: B2 and B4 (the two points only!), then respectively B22 and B24, B42 and B44... you get it.

Here's approximately the code I came up with, typed from memory:
Code:
``````Sub GraphTry()

Dim ws As Worksheet
Dim i As Long
Dim myChtObj As ChartObject

Set ws = Sheets("Overall")

(Left:=100, Width:=375, Top:=75, Height:=500)

With myChtObj.Chart
.ChartType = xlXYScatterLines
.HasLegend = True

For i = 1 To 700 Step 20
With .SeriesCollection.NewSeries
.Values = ws.Range("B" & i + 1, "B" & i + 3)

End With
Next

End With

End Sub``````

Of course, the problem is that code takes three points instead of two (like in B2:B4, instead of just B2 and B4). The macro recorder made me beat my head against the wall for a while because it simply records the two cells as "\$A\$2; \$A\$4" or something, which doesn't actually work.

I tried my best to google stuff like "vba chart non adjacent cells" but it always seems like the problems that people have are different, more complicated than mine, and there's talk of saving cells in arrays and stuff... which I've no clue about.
Course I could just manually change the work sheet so that the two time points are adjacent; but there's a very good chance that other analyses will require different time points, once again not adjacent, so I would just be constantly shuffling the worksheet around. Not an elegant solution. In fact I would prefer to have some dialogue where I would enter the time points, but at the moment my heads thinking about it. It would suffice to make this work.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi
Welcome to the board

in

Code:
``            ws.Range("B2", "B10")``

B2 and B10 are the limit cells from the range that includes B2, B10 and all the cells in between.

If you want just some specific cells, you can build a multi-area range using Application.Union, like

Code:
``            Application.Union(ws.Range("B2"), ws.Range("B10"))``

this means a range with only 2 cells, B2 and B10.

In the case of yourcode, try

Code:
``            .Values = Application.Union(ws.Range("B" & i + 1), ws.Range("B" & i + 3))``

Another way is to specify the multiarea range with a string, in this case the references separated by a ",".

Range("B2","B10") - B2, B10 and all the cells in between
Range("B2,B10") - just B2 and B10

Code:
``            .Values = ws.Range("B" & i + 1 & ",B" & i + 3)``

Awesome! Thank you. I knew the answer would be easy, but that's what you get without a real understanding of the basics of syntax... Works great.

You're welcome. Thanks for the feedback.

Replies
7
Views
266
Replies
7
Views
385
Replies
1
Views
275
Replies
2
Views
646
Replies
0
Views
750

1,211,798
Messages
6,104,031
Members
447,890
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back