Create multiple scatter graphs

[rathalex]

Hello!
I have data consisted of 18 columns. From these 18 columns I need to produce 9 graphs. However, i dont need to use entire columns for those,only last non empty cells. For instance, i need to take a value from last nonempty cell from a column A and B and use those two points to construct XY line plot. Then repeat that for C&D,E&F,G&H,I&J,etc. and add those lines to same chart. Could anyone help me with this task? Thank you!

 

[oldbrewer]

2	4	20	31						5	7
3	5	21	32						25	36
4	6	22	33
5	7	23	34
		24	35
		25	36
							this macro finds the last data pairs and puts them in columns J and K
										ready for plotting
							Sub Macro4()
							'
							' Macro4 Macro
							' Macro recorded 27/10/2017 by bob
							'
							'
							For j = 1 To 10
							If Cells(j, 1) = "" Then GoTo 50
							temp1 = Cells(j, 1)
							temp2 = Cells(j, 2)
							Next j
							50 Cells(1, 10) = temp1
							Cells(1, 11) = temp2
							For k = 1 To 10
							If Cells(k, 3) = "" Then GoTo 60
							temp1 = Cells(k, 3)
							temp2 = Cells(k, 4)
							Next k
							60 Cells(2, 10) = temp1
							Cells(2, 11) = temp2
							End Sub

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

2	4	20	31	10	20	44	55	101	120					5	7
3	5	21	32	11	19	55	66	102	125					25	36
4	6	22	33	12	18	66	77	103	130					12	18
5	7	23	34			77	88	104	135					77	88
		24	35					105	140					107	150
		25	36					106	145
								107	150
											this revised macro will deal with any number of columns
											and a max nuber of rows can be set
											Sub Macro4()
											'
											' Macro4 Macro
											' Macro recorded 27/10/2017 by bob
											'
											'
											For a = 1 To 9 Step 2
											Sum = Sum + 1
											For j = 1 To 10
											If Cells(j, a) = "" Then GoTo 50
											temp1 = Cells(j, a)
											temp2 = Cells(j, a + 1)
											Next j
											50 Cells(Sum, 15) = temp1
											Cells(Sum, 16) = temp2
											Next a
											End Sub

<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[rathalex]

Thank you Oldbrewer! Actually I dont need to put these values anywhere. I just need to select values from last nonempty cells and plot lines for each set. In other words, i cant really pick numbers for row or column cause it might be over thousands...I need excel to grab values from last nonempty cells and plot them.

 

[oldbrewer]

I have "picked" each pair of values and placed them somewhere convenient. You can now use the first pair and plot them, copy the chart 4 times and edit the data values in those 4 charts. Now when you add new data, each chart will update automatically when you run the macro. Unless I am misunderstanding you....

 

[rathalex]

Thanks for the reply! I actually need everything in One chart...is there way ti do this?

 

[oldbrewer]

so highlight the whole "block" of "pairs" and make your chart

 

[rathalex]

Not sure what you mean by that...I need Excel to create 9 graphs in one plot area. Why would I highlight and make chart if Excel could do it for me?

 

[oldbrewer]

you have to think what a scatter plot is - two points 1,1 and 3,3 joined by a line - easy. Now say you want to add another line between 2,2 and 5,5 - how are you going to do it.

I think I would make a second chart IDENTICAL in shape and size to the first one with 2,2 and 5,5 plotted, then move the second chart to be EXACTLY on top of the first chart....or make line charts each with only two data points...