Chart Ranges (Source Data)

A

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
I know how to create dynamic ranges for automatically updating charts; however,
my chart or rather the dynamic range is looking at a pivot table that is Top 10 (or 5, or 1 or whatever); however, when the pivot table is filtering based off of Top whatever, there are some months (horizontally across the top of the pivot table) that have totals of Zero. I can't figure out how to automatically hide the months that have total values of zero, and I need to for the sake of the chart automatically updating and eliminating (not displaying) the months with zeros.

Wondering if anyone knows of a way to accomplish updating the chart iwth months that only contain values >0
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Here is an example of my pivot table:
Part_Lot_Similarities_Template.xls
ABCDEFGHI
7CountofLOT_NUMBERENTRY_MONTH
8PART_NUMBERLOT_NUMBERNov-04Dec-04Jan-05Feb-05Mar-05Apr-05May-05
91007823-0860327320000000
1060517310000000
1160712310000000
1260103310000000
1360412330000000
1460727310000000
1561016310000000
1660427310000000
1751013310000000
1850404310000011
1950523310000000
2050324310000032
2160209310000000
2250120310000260
2350225310000005
2460112310000000
251007823-08Total00002108
Chart


So in this example I ultimately want the dynamic ranges
"INCIDENTS"
Code: 
=OFFSET(Chart!$C$25,0,0,1,COUNTA(Chart!$C$8:$IV$8))
and
"MONTHS":
Code: 
 =OFFSET(Chart!$C$8,0,0,1,COUNTA(Chart!$C$8:$IV$8))
to select only Mar-05 thru May-05 (G8:I8) for Months & (G25:I25) for Incidents

But currently it is making the ranges C8:I8 & C25:I25

Does this shed any light on my question....for someone to possibly help me?
 
Upvote 0
I am still trying to see if anyone has any ideas or knows of a solution on being able to eliminate cells (columns) in the counta portion of the formula for the dynamic range?
 
Upvote 0
I think I may have a partial solution...however, I don't really know how to write the macro
If I can hide the entire column based off of value greater than zero in the the total row (Row 25) then I'm getting somewhere;

Can someone show me how to do that?
 
Upvote 0
Okay, I'm getting somewhere, so this is kind of like journal entries to see if anyone can help me further.
Here's what I came up to hide columns that have zero values
Code: 
Sub Macro1()
For Each r In Range("C25:IV25")
   If r.Value = 0 Then
      r.EntireColumn.Hidden = True
   Else
      r.EntireRow.Hidden = False
   End If
Next
End Sub
Now I still have the situation where my dynamic range will still select even the hidden columns. Is there a way to make the dynamic range select only the range of the shown columns?
 
Upvote 0
I've gotten a little closer...please..please...someone take a look and see what I might be doing wrong.
Code: 
Sub Macro3()
'QTY RANGE SELECTION
    'START
StartCell = Range("$IV$25").End(xlToLeft).Select
    'END
StartCell2 = Range("$A$25").End(xlToRight).End(xlToRight).Select

'MONTH RANGE SELECTION
    'START
StartCell3 = Range("$IV$8").End(xlToLeft).Select
    'END
StartCell4 = Range("$A$8").End(xlToRight).End(xlToRight).Select

      
ActiveWorkbook.Names.Add Name:="Incidents", RefersTo:="=OFFSET(" & StartCell & ",0,0,1,COUNTA(" & StartCell3 & ":" & StartCell4 & "))"

End Sub
When I step through this macro...the StartCell selections are correct, I watch them on the Excel spreadsheet move to the appropriate cell.
When it tries to work through the ActiveWorkbook.names.Add I get an error. Run-time erro '1004':
The formula you typed contains an error.
For information about fixing common formula problems, click Help.
To get assistance in entering a function, click OK, then click Function on the Insert menu.
If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark (').
END, DEBUG, HELP
 
Upvote 0
I've revised the code a little bit, and it is a lot closer:
Code: 
Sub Macro3()
'QTY RANGE SELECTION
    'START
StartCell = Range("$IV$25").End(xlToLeft).Select
    'END
StartCell2 = Range("$A$25").End(xlToRight).End(xlToRight).Select

'MONTH RANGE SELECTION
    'START
StartCell3 = Range("$IV$8").End(xlToLeft).Select
    'END
StartCell4 = Range("$A$8").End(xlToRight).End(xlToRight).Select

      
Names.Add Name:="Incident", RefersTo:=StartCell & ":" & StartCell2
Names.Add Name:="Month", RefersTo:=StartCell2 & ":" & StartCell3
End Sub

This is at least going in and creating defined names of Incident & Month;
However, it is defining the Refers To: as ="True:True"
Any help /ideas/suggestions/SOLUTIONS for me?
 
Upvote 0
just updating this as if it were a journal in case anyone else could benefit from it at all..
the solution ended up being (after much trial/error):
Code: 
Private Sub CommandButton2_Click()
Sheets("Chart").PivotTables("PivotTable6").PivotCache.Refresh

For Each r In Range("C5:IV5")
If r.Value = 0 Then
r.EntireColumn.Hidden = True
    Else
If r.Value = "" Then
   r.EntireColumn.Hidden = True
   Else
r.EntireColumn.Hidden = False
    End If
    End If
Next

StartCell = Cells(5, 255).End(xlToLeft).Column
    'END
StartCell2 = Cells(5, 3).End(xlToRight).Column
Cells(5, StartCell).Resize(1, StartCell2).Name = "Incidents"

'MONTH RANGE SELECTION
    'START
StartCell3 = Cells(4, 255).End(xlToLeft).Column

    'END
StartCell4 = Cells(4, 3).End(xlToRight).Column
      
Cells(4, StartCell3).Resize(1, StartCell4).Name = "Months"

End Sub

That hid columns in the pivot that had totals of zero, then created defined name ranges (Incidents & Months) for use in automatically updating a chart.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
272
ashleighbrown
A
P
  • Question
How do I make these two pivot charts from this specific table? I can't figure it out, and this is a very common table format.
Replies
1
Views
223
awoohaw
awoohaw
R
  • Question
Formatting data series for chart created via Slicer and Pivot Table
Replies
6
Views
284
awoohaw
awoohaw
T
  • Question
How to *generate* data for a waterfall chart (not the chart itself)
Replies
0
Views
85
trippknightly
T
P
  • Question
Dynamic chart based on month
Replies
7
Views
315
skillilea
S

Forum statistics

Threads
1,214,791
Messages
6,121,611
Members
449,038
Latest member
apwr

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top