Need to plot a range without inner quotes

[kosmoclone]

Hi,
I'm trying to plot a range of numbers:
Col G Col H
10 4.5
20 6
30 8.3

I'm selecting the data using
[G9].select
selection.End(xlDown).select
k = ("G9:" & "G" & Activecell.Row)

[H9].select
selection.End(xlDown).select
o = ("H9:" & "H" & Activecell.Row)

Range(k,0).select
'This will actually be viewed as Range("G9:G13","H9:H13") and I need it to be: Range("G9:G13, H9:H13) 'Notice the inner quotes are gone'

Activesheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter

I want the 10,20,30 column to be the X axis values and not to be plotted.

Thanks.

 

[JackDanIce]

Welcome to the board.

Does this work?

k = Range("G9").End(xlDown).Row
o = Range("H9").End(xlDown).Row
Range("G9:G" & k & ",H9:H" & o).Select

 

[kosmoclone]

Hi Jack, thanks for the reply.

No, it still plots both columns of data.

 

[JackDanIce]

I'm not sure what to suggest because when I step through this code

k = Range("G9").End(xlDown).row
o = Range("H9").End(xlDown).row
Range("G9:G" & k & ",H9:H" & o).Select
Range("G9:G" & k , "H9:H" & o).Select

It does not group my cells in an identical manner (i.e. the red line selects an area different to the green line). However, your reply suggests the code is selecting the same area ... so not sure if I can suggest anything to solve.

Hopefully someone can answer your query

 

[kosmoclone]

Thanks for trying.

If I select cells G9:H13 and plot the data it looks correct. I just can't figure out the vba code to automate this.

 

[JackDanIce]

How exactly are you selecting those cells? Is it:

1) Click and hold the left mouse button on G9 and then drag to H13?
Green line in my code above

2) Click and hold the left mouse button on G9, drag down to G13, release the mouse button, then whilst holding down the control key, click on H9, hold the left mouse button, drag down to H13, then let go of the mouse button and the control key
Red line in my code above

 

[kosmoclone]

Both of those ways work if I do it manually, just doesn't work in vb.

 

[JackDanIce]

I don't think you are reading or executing my code correctly. My reply was the line coloured green in the code I suggested, selects the cells as described in option 1.

The line coloured red in the code I suggested, selects the cells as described in option 2.

I don't understand what you are saying with:

Both of those ways work if I do it manually, just doesn't work in vb.

Because they both work in the code I provided i.e. in VBA. You need to choose which colour line satisfies your needs.

 

[kosmoclone]

I don't think you are reading or executing my code correctly. My reply was the line coloured green in the code I suggested, selects the cells as described in option 1.

The line coloured red in the code I suggested, selects the cells as described in option 2.

I don't understand what you are saying with:

Because they both work in the code I provided i.e. in VBA. You need to choose which colour line satisfies your needs.

I see. I ran through your code and I see now that it selects the range in different ways. However either way plots both columns of numbers when ran in VB. I'm using 2007, does this make a difference? Do I need to insert the chart using additional lines?

By manually, I mean if I don't use VB and just plot the data by selecting it with the mouse and inserting a XY scatter plot.

Thanks.

 

[JackDanIce]

Can you post the full code of what you have?

If you can do it manually, have you tried having Excel record your macro and then looking at the code to see what it's doing? Maybe compare this back to the code you currently have?