Help Creating a Dynamic Stacked Chart

Hello EE,

Can you please help me create a stacked similar to the one in my sample data attached here.  My data is in columns 5-10, but it can grow to column 25.  I do not want the legend to show if there are no data in columns 11-25 as an example.

Thanks in advance for your help.
LVL 1
fb1990Asked:
Who is Participating?
 
xtermieConnect With a Mentor Commented:
Here is a macro that will do what Rob says...hide empty value rows.
That would do the trick I think :)
I have attached an image of your sample file after I run the macro I am attaching below.

Sub HideEmptyRows()
Dim r As Range
Dim wb As Workbook
Dim ws As Worksheet
Dim ccol1, ccol2, ccol3, ccol4
'Unhide everything
Set r = ActiveSheet.Columns("A:A")
r.EntireRow.Hidden = False
Set r = Nothing
'Hide rows where no values in B, C, D, E
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row
ccol1 = 2
ccol2 = 3
ccol3 = 4
ccol4 = 5
For n = nFirstRow To nLastRow
    MsgBox Cells(n, ccol1).Value
    If Cells(n, ccol1).Value = 0 And Cells(n, ccol2).Value = 0 And _
    Cells(n, ccol3).Value = 0 And Cells(n, ccol4).Value = 0 Then
                Rows(n).EntireRow.Hidden = True
    End If
    Next
End Sub

Open in new window

EE_Graph.PNG
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You forgot to attache the sample workbook.
0
 
fb1990Author Commented:
My apologies.  I have attached the file now.  You have been a great helper.
My-Sample2.xlsx
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
xtermieCommented:
1.      Click the File tab and choose Options. In Excel 2007, click the Office button and then click Excel options. In Excel 2003, choose Options from the Tools menu and skip to #3.
2.      Choose Advanced in the left pane.
3.      In the Display options for this worksheet section, choose the appropriate sheet from the drop-down menu.
4.      Uncheck the Show a zero in cells that have zero value option

Read along here
http://www.techrepublic.com/article/how-to-suppress-0-values-in-an-excel-chart/
0
 
fb1990Author Commented:
This does not work for me.  I need a solution that will change the chart dynamically on refresh  because the data can grow or shrink.

Thanks for your help
0
 
Rob HensonFinance AnalystCommented:
If you hide the rows that have blanks they will not appear in the chart.

Initially The EM# references still appearred in the legend. Go to the Select Data window and click the Switch Rows and Columns twice, ie swap and then swap back.

Alternatively you could make the data range dynamic based on a count of entries in one of the data columns. If that sounds like a better approach than hiding rows, let me know and I will advise.

Thanks
Rob H

EDIT: Dynamic Range doesn't seem to be working for me, I am sure it is possible so maybe another EE member will be able to advise.
1
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
As per your other question:

https://www.experts-exchange.com/questions/28963464/Preventing-Null-values-from-plotting-in-Excel-Charts.html

Solved by Neeraj with the use of Range Names in the chart.

You could do the same with the named ranges but make them dynamic (if they weren't already in that previous solution).
1
 
xtermieCommented:
Good points and code provided
0
All Courses

From novice to tech pro — start learning today.