• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

A bar graph chart that leaves out bars if value is zero / empty

Hui

Excel Ninja
Staff member
Neb

So if you have 6 bars and 1 data point is zero you only want to have 5 bars ?

So you don't want a gap

Is that correct?
 

Hui

Excel Ninja
Staff member
Have you tried manually hiding the rows with the zero data?


As well as hiding you can also Group Rows to hide them,

Select the Row with the zero data and Alt Shift Right Arrow

Once the row is grouped

Click on the - on the left margin to hide the row

Click on the + to unhide the row

Alt Shift Left Arrow to remove the group

This way you have a visual clue that there are hidden rows
 

neb

New Member
Hi Hui


I am trying to avoid doing that as I have a full sheet of columns and the data changes monthly - something that was zero this month may be populated next month etc etc - the data is organised like this:


KB ZZ DD QR

12 0 33 44 .... etc etc across an entire sheet of columns, 7 x sheets of this.


So manually hiding columns is not an option really!
 

Hui

Excel Ninja
Staff member
Have you tried Grouping Columns as mentioned above ?


Is automatically hiding Columns an option ?


can you post your data somewhere ?
 

neb

New Member
How do you automatically hide columns? If there is some way that a column automatically hides when there is only zeros / blanks that could work.


Where/how could I post my data? Can you post data on this site?


Thanks
 

Hui

Excel Ninja
Staff member
Neb

I have posted below 2 macros

They need to be pasted into a Code Module in VBA


The Names are self explanatory


To use assign each to a shape or button on your worksheet or you can run them manually


Important:

Select a cell in a left hand cell of a row which contains your data

eg: in your case 12 0 33 44 .... select cell 12 and run the Hide_Cols macro

[pre]
Code:
Sub Hide_Cols()

Dim c As Range

Range(ActiveCell, Selection.End(xlToRight)).Select
For Each c In Selection
If c.Value = 0 Or c.Value = "" Then c.EntireColumn.Hidden = True
Next

End Sub

The Unhide_All_Cols macros does just that


Sub Unhide_All_Cols()

Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireColumn.Hidden = False
Range("A1").Select

End Sub
[/pre]
You can't post files here on Chandoo.org but have a read of

http://chandoo.org/forums/topic/posting-a-sample-workbook

for some ideas
 
Top