Solved

# Dynamic Ranges and Sparklines

Posted on 2014-08-26
Medium Priority
1,511 Views
I would like to create normal sparklines for a data matrix (horizontal lines next to a table of horizontal data in series).
The width of the table grows and shrinks depending on the state of the sheet.
I want the lines to adjust accordingly.
I tried creating a name that references the 2D matrix using OFFSET, with I'll call "rngMatrix" here.
If it set the sparklines data range to "rngMatrix" - voila!  They update.
BUT....
The moment I hit enter, rngMatrix gets converted to an absolute reference - so the dynamic-ness is gone.
Can't figure out why.
SparkLines.xlsx
0
Question by:shacho
• 4
• 4

LVL 27

Accepted Solution

Glenn Ray earned 2000 total points
ID: 40286853
Rory Archibald solved a similar problem to yours in this EE question.  I've adapted his solution for you.

Ideally, the sparklines should be able to use a dynamic range, but as you noted, if one has only one named range listed, it reverts to the absolute cell range.  To get around it, you create named ranges for each of your sparklines and then list them all in the Data Range.

So, I created three named ranges:
rngMatrix7: =OFFSET(Sheet1!\$H\$7,0,0,1,COUNT(Sheet1!\$H\$7:\$Z\$7))
rngMatrix8: =OFFSET(Sheet1!\$H\$8,0,0,1,COUNT(Sheet1!\$H\$8:\$Z\$8))
rngMatrix9: =OFFSET(Sheet1!\$H\$9,0,0,1,COUNT(Sheet1!\$H\$9:\$Z\$9))

Then I added them all to the Data Range:

This seems to preserve the dynamic ranging you're looking for.  Modified workbook attached.

Regards,
-Glenn
EE-SparkLines.xlsx
0

Author Comment

ID: 40286942
Ugh - I had a feeling that was going to be the answer... (There are >150 rows).
But hey, whatever works, eh?
Thanks for the solution!

Cheers,

Mike
0

LVL 27

Expert Comment

ID: 40286964
Don't give up!  I've written a couple of macros that will automatically create the necessary number of dynamic ranges AND assign them to the sparkline group.
Option Explicit
Sub Create_Dynamic_Ranges()
Dim x As Integer
For x = 7 To 27 'or any upper limit you need
ActiveWorkbook.Names.Add Name:="rngMatrix" & x, RefersToR1C1:= _
"=OFFSET(Sheet1!R" & x & "C8,0,0,1,COUNT(Sheet1!R" & x & "C8:R" & x & "C26))"
ActiveWorkbook.Names("rngMatrix" & x).Comment = ""
Next x
End Sub

Sub Expand_Sparkline()
Dim x As Integer
Dim strRange As String
Dim strSource As String

For x = 7 To 27
strRange = strRange & "\$G\$" & x & ","
strSource = strSource & "rngMatrix" & x & ","
Next x
strRange = Left(strRange, Len(strRange) - 1)
strSource = Left(strSource, Len(strSource) - 1)

Range("G7").Select
ActiveCell.SparklineGroups.Item(1).Modify Location:=Range(strRange), _
SourceData:=strSource
End Sub

Modified macro-enabled workbook attached.  I've only created the first twenty (rows 7-26).

-Glenn
EE-SparkLines.xlsm
0

Author Comment

ID: 40287164
Nifty.  I took a slightly different approach, but thanks for the suggestion!!

Sub MakeSparklineRefs()
Dim CTR As Integer
Dim NameName As String, NameRefersTo As String
Dim SparkLineString As String
With Me.Range("rngMatrix").Rows
For CTR = 1 To .Count
NameName = "rngMatrix_" & Format(CTR, "000")
NameRefersTo = "=INDEX(rngMatrix," & CTR & ",)"
SparkLineString = SparkLineString & ", " & NameName
Next CTR
End With
Debug.Print Mid(SparkLineString, 3)
End Sub
0

LVL 27

Expert Comment

ID: 40287178
Very cool; nice and compact code there.
0

Author Comment

ID: 40287317
Cheers.  The references are working perfectly.  I feel like a complete geek, and lovin' it.
0

LVL 27

Expert Comment

ID: 40288081
The fact that you named your dynamic range with the "rng" prefix was telling. :-)

-Glenn
0

Author Comment

ID: 40289269
Thanks for the compliment!  If you end up using that code, be forewarned that the output to the immediate window will wrap if it's long enough.  If that happens, a line break will sneak into the clipboard when you copy it.  Make sure you paste it into Word (or whatever), delete the line break, recopy and paste into the Sparklines dialog box.  I discovered this the hard way...

Cheers,

Mike
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month17 days, 6 hours left to enroll