cell addresses selection for chart source
 

I am trying to create a macro to select a range of cells from the same
row, but the columns may or may not be contiguous and the cell
references are not constant (they will change every month). Then use
this selection for the SetSourceData in a chart.

For example if I manually select the following using the CNTL key:
a1,a2,a3,a4,a5, a7,a8,a9,a10,a11 (as in a 2 weeks worth of data)
I would use this selection in the source for a chart.

Should I start the cell selection from Starting Point A and go through
a loop to select the necessary cells and place into an array? Or
should I use a set range command? I hope I am being clear. Any help
would be appreciated.

Thank you.

Leslie

cell addresses selection for chart source
 

set rng = Range("A1:A5,A7:A11")

--
Regards,
Tom Ogilvy

wrote in message
ps.com...
I am trying to create a macro to select a range of cells from the same
row, but the columns may or may not be contiguous and the cell
references are not constant (they will change every month). Then use
this selection for the SetSourceData in a chart.

For example if I manually select the following using the CNTL key:
a1,a2,a3,a4,a5, a7,a8,a9,a10,a11 (as in a 2 weeks worth of data)
I would use this selection in the source for a chart.

Should I start the cell selection from Starting Point A and go through
a loop to select the necessary cells and place into an array? Or
should I use a set range command? I hope I am being clear. Any help
would be appreciated.

Thank you.

Leslie

cell addresses selection for chart source
 

Leslie -

If you need to incorporate some kind of test to determine which cells to include,
start with the entire range, and loop through to add the appropriate cells:

Dim rngCell as Range
Dim rngWholeRange as Range
Dim rngToChart as Range

Set rngWholeRange = Range("A1:A11")

For Each rngCell in rngWholeRange.Cells
If {rngCell passes the test} Then
If rngToChart Is Nothing Then
Set rngToChart = rngCell
Else
Set rngToChart = Union(rngToChart, rngCell)
End If
End If
Next ' rngCell

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

wrote:

I am trying to create a macro to select a range of cells from the same
row, but the columns may or may not be contiguous and the cell
references are not constant (they will change every month). Then use
this selection for the SetSourceData in a chart.

For example if I manually select the following using the CNTL key:
a1,a2,a3,a4,a5, a7,a8,a9,a10,a11 (as in a 2 weeks worth of data)
I would use this selection in the source for a chart.

Should I start the cell selection from Starting Point A and go through
a loop to select the necessary cells and place into an array? Or
should I use a set range command? I hope I am being clear. Any help
would be appreciated.

Thank you.

Leslie

cell addresses selection for chart source
 

Jon, the code has worked great! Thank you for saving me hours of
frustration.
I was able to put in TESTING criteria and refer to ranges using
variables instead of the constants which I used in my example.

cell addresses selection for chart source
 

Glad to help.

- Jon

looloo wrote:

Jon, the code has worked great! Thank you for saving me hours of
frustration.
I was able to put in TESTING criteria and refer to ranges using
variables instead of the constants which I used in my example.

cell addresses selection for chart source
 

Jon,

May I ask for a bit more detail from your response to the above? Your
recommendation excluded (I believe) the object reference. Amateur logic told
me to add these lines after Dim statements, but of course they're incorrect.
Would you mind helping me to correct my error please?

Worksheets("Chart").ChartObjects("Chart4").Activat e
ActiveSheet.SetrngWholeRange = Range("B163:R171")

Also, my "test" using the Len property and "or" statement may also be
incorrect, but the code hasn't passed to that point yet.

If {Len(rngCell.Value) = 0 Or rngCell.Value = 0}Then

Essentially hoping for the entire subroutine. Any help is much appreciated.

"Jon Peltier" wrote:

Glad to help.

- Jon

looloo wrote:

Jon, the code has worked great! Thank you for saving me hours of
frustration.
I was able to put in TESTING criteria and refer to ranges using
variables instead of the constants which I used in my example.