Variable range graph

rukid - Jun 29, 2009 at 12:27 PM
 roheet - Nov 9, 2009 at 07:44 AM
I am trying to make a macro in excel that makes graphs from a variable range. This is what I have so far and I don't know why it does work.

Sub Macro1()
' Macro1 Macro

Range(Selection, Selection.End(xlDown)).Select
n = Range("A5", Range("A5").End(xlDown)).Count
f = Range(Selection, Selection.End(xlDown)).Select
ActiveChart.SetSourceData Source:=Range(n, f) 'it fails here
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
End Sub

I've tried many different things and I don't know how to put in a range that will select to the last non empty cell in a column.
Thank you so much for your help.

4 responses

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jun 30, 2009 at 06:17 AM
Hi rukid
Instead of Range(n, f) use Range("A5:D" & n)
Hey thank you for the reply,
I tried what you suggested but it does not work as the columns that the data are in are not from a through d continuously, they are in a,d,f,h,j do you know of a remedy for this? Thank you again.
The other issue that I just noticed is that if I change the length of the data meaning make the columns have more or less data, the macro does not change the range to encompass the whole column. Thanks for any help.
My dear friend , i know it sounds stupid for me to reply but if u try this code
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
jusat try and will this surve youe purpose