Graphs from two non-adjacent cells

MisterV

New Member
Joined
Mar 4, 2016
Messages
7
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.

AB
TimeData
140
235
338
430
539
645
747
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")


    Set myChtObj = ActiveSheet.ChartObjects.Add _
        (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.

Appreciate your wise counsel.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,879
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))
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,879
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

In your case:

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

MisterV

New Member
Joined
Mar 4, 2016
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,353
Messages
5,595,662
Members
414,007
Latest member
bongda360org

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top