Excel Dynamic Chart Range

Hello,

I am struggling with creating a chart that will automatically update it's range in excel.  I have tried several solutions (defining names, using a table, etc.) but I run in to the same problem every time.  The point is for the data to be copy pasted in to the correct month on the "master" worksheet and have it automatically update the charts for all of the stores.  The problem with the dynamic charts I have tried so far is they include all of the blank cells as they are not truly blank (they have formulas to find the data from the master tab).  I need to either find a way to have the chart ignore those cells until they are filled with actual numbers, or use a macro that will add the next row of data to each chart.

Thank you so much for your help,
Mark
Example.xlsx
Mtl321Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
byundtConnect With a Mentor Commented:
I replaced all the code in the worksheet codepanes with the following sub in Master worksheet code pane:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Sh As Worksheet
    Dim oChrt As ChartObject
    Dim sName As String, szSeries As String
    Dim rg As Range, rgLast As Range, rgSource As Range, rgY As Range

    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.ChartObjects.Count > 0 Then
            For Each oChrt In Sh.ChartObjects
                szSeries = oChrt.Chart.SeriesCollection(1).Formula
                szSeries = Split(szSeries, ",")(2)
                sName = Split(szSeries, "!")(0)
                If Left(sName, 1) = "'" Then sName = Mid(sName, 2, Len(sName) - 2)
                Set rg = Worksheets(sName).Range(Split(szSeries, "!")(1))
                If rg.Rows.Count >= rg.Columns.Count Then
                    Set rg = Intersect(rg.EntireColumn, rg.CurrentRegion)
                Else
                    Set rg = Intersect(rg.EntireRow, rg.CurrentRegion)
                End If
                
                On Error Resume Next
                Set rgY = rg.SpecialCells(xlCellTypeFormulas, Value:=xlNumbers + xlTextValues)
                If rgY Is Nothing Then Set rgY = rg.SpecialCells(xlCellTypeConstants, Value:=xlNumbers + xlTextValues)
                On Error GoTo 0
    
                Set rgSource = rgY.CurrentRegion
                If rgY.Rows.Count >= rgY.Columns.Count Then
                    Set rgLast = Intersect(rgY.Cells(rgY.Cells.Count).EntireRow, rgSource)
                    Set rgSource = Worksheets(sName).Range(rgSource.Cells(1, 1), rgLast.Cells(rgSource.Columns.Count))
                Else
                    Set rgLast = Intersect(rgY.Cells(rgY.Cells.Count).EntireColumn, rgSource)
                    Set rgSource = Worksheets(sName).Range(rgSource.Cells(1, 1), rgLast.Cells(rgSource.Rows.Count))
                End If
                oChrt.Chart.SetSourceData rgSource
            Next oChrt
        End If
    Next

End Sub

Open in new window

updated-exampleQ28169891.xlsm
0
 
Mtl321Author Commented:
Thanks for such a quick reply,

Yes I did look at those already, their problems are all slightly different.  There was someone who had a similar problem, but the solution gave them the same problem that I am having.

Thanks,
Mark
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MacroShadowCommented:
Try the attached file.
1. Change chart type to Line with Markers.
2. Change the formula in the chart source from =Master!X to =IF(Master!X=0,NA(),Master!X)
2. Automatically update the range using the Worksheet_Change event.
Example.xlsm
0
 
Mtl321Author Commented:
I do not see the “Worksheet_Change event” should it be under macros?
0
 
MacroShadowCommented:
Open the VBE (Visual Basic Editor) Alt+F11. See this for details http://www.excel-vba.com/vba-prog-1-1-editor.htm
0
 
Mtl321Author Commented:
I am currently on a mac, would that make a difference?  I can gain access to a PC if necessary (and the final code I need is for a PC).
0
 
MacroShadowCommented:
No idea, never used a mac.
0
 
Mtl321Author Commented:
I can not get the macro to run (I see it) it just opens up a new window that says "macros" and is asking for a name when I try to run it.
0
 
MacroShadowCommented:
The macro will run automatically every time a change is made to the worksheet. In other words just copy the code to your worksheet and forget about it, it will do the heavy lifting itself.
0
 
Mtl321Author Commented:
Hello again, I am so sorry for taking such a long time to reply I have been out of town.  I now see how the macro is working.  My problem is the chart has a ton of blank space to the right of the last month that the graph is plotting, I want to have the date extended for several years in advance, but the chart is updating the range to include every date on the sheet.  

I have attached the same sample worksheet showing the problem *(I only need to know how to do it on the "total" worksheet, and I can figure out how to copy it from there) .  Ideally I would like the chart range to only update to the line right before the "#N/A" rows start so the chart will not include the white space.  Thank you so much for all of your help!!!

Mark
updated-example.xlsm
0
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.

All Courses

From novice to tech pro — start learning today.