MrExcel.com - Your One Stop for Excel Tips & Solutions

Using variables to graph a calculated range.

Posted by John F. on April 12, 2001 10:14 PM

I am trying to do something that has been elusive although I can't imagine difficult. I am trying to use VBA to graph a range that is calculated based on the first empty row (I only want the last 15 entries in the graph. I can determine the first empty cell and I know how to use variables with the Cells object for specifying a SINGLE CELL but cannot figure out how to use it to define a range of cells. Can anyone help out here? I would be greatly appreciated.

Posted by Dave Hawley on April 13, 2001 3:24 AM

Hi John

Try this


Sub AddRange()
Dim Rrange As Range
'Written by OzGrid Business Applications
'www.ozgrid.com

Set Rrange = Range(Range("A1").End(xlDown), Range("A1").End(xlDown).Offset(15, 0))
Rrange.Select
Set Rrange = Nothing
End Sub

Dave
OzGrid Business Applications

Posted by John F. on April 13, 2001 11:07 PM


Dave,

Your solution worked with selecting the range. However, I ran into another problem shortly thereafter. In the statement,

ActiveChart.SetSourceData Source:=Sheets(Sheetneeded).Range(???What goes here???), PlotBy:=xlColumns

In the ???What goes here??? I've tried the variable name Rrange with and without quotation marks, the whole Range(range("A1")...etc. statement, and just about everything else I can think of and it keeps giving me an error message at this line. I did not use the:

Set Rrange = Nothing

statement prior to this line so that should not be the problem. Please help if you can. Thanks!!!

Posted by Dave Hawley on April 14, 2001 9:57 PM

Hi John

I haven't tested this, but I would think it would be:

ActiveChart.SetSourceData Source:=Sheets(Sheetneeded).Range(Rrange.address ), PlotBy:=xlColumns


Dave


OzGrid Business Applications

Posted by John F. on April 17, 2001 12:07 PM

Dave,

Thanks a bunch for your help. I just have (hopefully) one more question.
If I am using non-contiguous ranges (2 of them) to graph, how do I select
them to use in a graph?

Example:

Dim Rrange As Range
Dim Srange As Range
Sheets(Sheetneeded).Select
Set Rrange = Range(Range("A1").End(xlDown), Range("A1").End(xlDown).Offset(-15, 0))
Set Srange = Range(Range("I1").End(xlDown), Range("I1").End(xlDown).Offset(-15, -3))
Range(Rrange, Srange).Select

This ends up selecting a range of both defined ranges and everything in between. Thanks
in advance for your help!!

Posted by Peter on April 17, 2001 5:26 PM

Thanks a bunch for your help. I just have (hopefully) one more question.


Union(Rrange, Srange).Select