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.
The moment I hit enter, rngMatrix gets converted to an absolute reference - so the dynamic-ness is gone.
Can't figure out why.
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.

Glenn RayExcel 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:
sparkline dynamic ranges
This seems to preserve the dynamic ranging you're looking for.  Modified workbook attached.


Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
shachoAuthor 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!


Glenn RayExcel 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)
    ActiveCell.SparklineGroups.Item(1).Modify Location:=Range(strRange), _
End Sub

Open in new window

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

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

shachoAuthor 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 & ",)"
            Me.Names.Add NameName, NameRefersTo
            SparkLineString = SparkLineString & ", " & NameName
        Next CTR
    End With
    Debug.Print Mid(SparkLineString, 3)
End Sub

Open in new window

Glenn RayExcel VBA DeveloperCommented:
Very cool; nice and compact code there.
shachoAuthor Commented:
Cheers.  The references are working perfectly.  I feel like a complete geek, and lovin' it.
Glenn RayExcel VBA DeveloperCommented:
The fact that you named your dynamic range with the "rng" prefix was telling. :-)  

I'm saving this thread.

shachoAuthor 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...


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.