cell addresses selection for chart source

[looloopayne]

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

 

[Tom Ogilvy]

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

 

[Jon Peltier]

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/
_______

 

[looloo]

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.

 

[Jon Peltier]

Glad to help.

- Jon

 

[Guest]

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").Activate
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.