# Dynamic Ranges and Sparklines

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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel VBA DeveloperCommented:
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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
Excel VBA DeveloperCommented:
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 Commented:
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
Excel VBA DeveloperCommented:
Very cool; nice and compact code there.
0
Author Commented:
Cheers.  The references are working perfectly.  I feel like a complete geek, and lovin' it.
0
Excel VBA DeveloperCommented:
The fact that you named your dynamic range with the "rng" prefix was telling. :-)

-Glenn
0
Author Commented:
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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.