Auto-set Chart Data Range

Referring to an earlier post in this newsgroup entitled 'Cell addresses
selection for chart source,' I'd like to ask for a bit more detailed advice.
I believe the code that Jon posted is the solution to my challenge, however,
I'm not versed in VB to do the easy part. I assume his recs left out the
object references. With that, I tried my hand to no avail...as follows.
Would someone be able to help with the remaining code to complete the
Subroutine?

Worksheets("Chart").ChartObjects("Chart4").Activate
ActiveSheet.SetrngWholeRange =3D Range("B163:R171")



Also, I defined a pretty rudimentary "test" statement.


For Each rngCell In rngWholeRange.Cells
If {Len(rngCell.Value) =3D 0 Or rngCell.Value =3D 0}Then


Is my syntax correct? Thanks in advance for your help


-----------------FROM EARLIER POST---------------------------

Private Sub CommandButton1_Click()
Dim rngCell As Range
Dim rngWholeRange As Range
Dim rngToChart As Range

SetrngWholeRange =3D Range("B163:R171")

For Each rngCell In rngWholeRange.Cells
If {Len(rngCell.Value) =3D 0 Or rngCell.Value =3D 0}Then
If rngToChart Is Nothing Then
Set rngToChart =3D rngCell
Else
Set rngToChart =3D Union(rngToChart, rngCell)
End If
End If
Next 'rngCell

End Sub

Pete -

You should reply to the old thread, so we can see more of the problem
statement. I have no recollection of this particular post (no offense).

Also, I'm not sure what this line means:

ActiveSheet.SetrngWholeRange =3D Range("B163:R171")

I assume the =3D just means =, but it's "ActiveSheet.SetrngWholeRange"
that throws me.

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

Pete Merenda wrote:

> Referring to an earlier post in this newsgroup entitled 'Cell addresses
> selection for chart source,' I'd like to ask for a bit more detailed advice.
> I believe the code that Jon posted is the solution to my challenge, however,
> I'm not versed in VB to do the easy part. I assume his recs left out the
> object references. With that, I tried my hand to no avail...as follows.
> Would someone be able to help with the remaining code to complete the
> Subroutine?
>
> Worksheets("Chart").ChartObjects("Chart4").Activate
> ActiveSheet.SetrngWholeRange =3D Range("B163:R171")
>
>
>
> Also, I defined a pretty rudimentary "test" statement.
>
>
> For Each rngCell In rngWholeRange.Cells
> If {Len(rngCell.Value) =3D 0 Or rngCell.Value =3D 0}Then
>
>
> Is my syntax correct? Thanks in advance for your help
>
>
> -----------------FROM EARLIER POST---------------------------
>
> Private Sub CommandButton1_Click()
> Dim rngCell As Range
> Dim rngWholeRange As Range
> Dim rngToChart As Range
>
> SetrngWholeRange =3D Range("B163:R171")
>
> For Each rngCell In rngWholeRange.Cells
> If {Len(rngCell.Value) =3D 0 Or rngCell.Value =3D 0}Then
> If rngToChart Is Nothing Then
> Set rngToChart =3D rngCell
> Else
> Set rngToChart =3D Union(rngToChart, rngCell)
> End If
> End If
> Next 'rngCell
>
> End Sub

Pete -

I wasn't trying to blow you off, I just didn't remember, and your follow
up didn't have enough to jar my memory. Then I stumbled upon the last
post of the earlier thread, and looked up the message ID in the Google
archive.

Here was my code:

' start snippet
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
' end snippet

You might need to reference the starting range:

Set rngWholeRange = ActiveSheet.Range("A1:A11")

or

Set rngWholeRange = _
ActiveWorkbook.Worksheets("Sheet1").Range("A1:A11")

You also then have to do something with the range, something *like* this:

ActiveWorkbook.Worksheets("Sheet1").ChartObjects(1) _
.Chart.SeriesCollection(1).Values = rngToChart

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

Pete Merenda wrote:

> Referring to an earlier post in this newsgroup entitled 'Cell addresses
> selection for chart source,' I'd like to ask for a bit more detailed advice.
> I believe the code that Jon posted is the solution to my challenge, however,
> I'm not versed in VB to do the easy part. I assume his recs left out the
> object references. With that, I tried my hand to no avail...as follows.
> Would someone be able to help with the remaining code to complete the
> Subroutine?
>
> Worksheets("Chart").ChartObjects("Chart4").Activate
> ActiveSheet.SetrngWholeRange =3D Range("B163:R171")
>
>
>
> Also, I defined a pretty rudimentary "test" statement.
>
>
> For Each rngCell In rngWholeRange.Cells
> If {Len(rngCell.Value) =3D 0 Or rngCell.Value =3D 0}Then
>
>
> Is my syntax correct? Thanks in advance for your help
>
>
> -----------------FROM EARLIER POST---------------------------
>
> Private Sub CommandButton1_Click()
> Dim rngCell As Range
> Dim rngWholeRange As Range
> Dim rngToChart As Range
>
> SetrngWholeRange =3D Range("B163:R171")
>
> For Each rngCell In rngWholeRange.Cells
> If {Len(rngCell.Value) =3D 0 Or rngCell.Value =3D 0}Then
> If rngToChart Is Nothing Then
> Set rngToChart =3D rngCell
> Else
> Set rngToChart =3D Union(rngToChart, rngCell)
> End If
> End If
> Next 'rngCell
>
> End Sub