Variable range graph

[Closed]
Report
-
 roheet -
Hello,
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("A5").Select
Range(Selection, Selection.End(xlDown)).Select
n = Range("A5", Range("A5").End(xlDown)).Count
Range("D5").Activate
f = Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Shapes.AddChart.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 replies

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
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("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Shapes.addChart.Select
jusat try and will this surve youe purpose