Update multiple sparklines

[ErikJan]

If I have different cells in my spreadsheet with sparklines (not by definition in one group), is there a way to update the range for all of them easily...? I haven't tried but would something like this work?

With worksheets.sparklines
.rng=<my upated range>
End With

[HansV]

You can set the source range for a single sparkline or for a sparkline group, but I don't see how you would set the source range for multiple unrelated sparklines.

[ErikJan]

OK, so could I group all my Sparklines first then? If so, how would I change the range?

[HansV]

Let's say your group consists of 3 sparklines, in E1, P3 and C6.
The new source range consists of E17:H17, G19:J19 and F13:I23.
The line of code you need is

Range("E1").SparklineGroups(1).ModifySourceData "E17:H17,G19:J19,F23:I23"

We only have to mention one of the cells in the group.

[ErikJan]

I still see strange things...

I now have three Sparklines in a group, the first one is in a cell called "SparkLineOwner":

This: [SparkLineOwner].SparklineGroups(1).SourceData
Gives this: "Data_Calculated!O2:O5000,Data_Calculated!N2:N5000,Data_Calculated!Q2:Q5654"

But this: Range([SparkLineOwner].SparklineGroups(1).SourceData).Rows.Count
Gives 4999

[HansV]

That is not surprising. Rows.Count returns the number of rows in the first areaof the source range, i.e. in O2:O5000. This is not specific to a source range. Try the following in the Immediate window:

? Range("A1:A3,A6:A10,A15:21,A28:A36").Rows.Count

This will return 3, the number of rows in A1:A3.

[ErikJan]

Thanks Hans,

I think I can find my way here now. This code allows me to step through each sparkline (in a group or not):

Code: Select all

Dim oSparkGroup As SparklineGroup, i As Integer, j As Integer
For Each oSparkGroup In ActiveSheet.Cells.SparklineGroups
    i = oSparkGroup.Count
    For j = 1 To i
     Debug.Print i, oSparkGroup.Item(j).SourceData
    Next
Next

I can now use this as my basis to update all my sparklines and give them the same end-row (and more), no matter what the user initially sets.