msquared99
Member
I had a post where I wanted to suppress either 0's or #N/A's in pie chart and keep them from showing up in the legend.
My next issue was how do I do this in a bar chart?
Scenario:
My titles are in column A and values in coulmn B starting in cell A1.
Item A 8
Item B 0
Item C 9
Item D 0
Item E 5
If I make each item a series, I'll have five different series.
Now If I build a column chart it is going to display Item B and leave a blank between Item A and Item C. Here is my work around.
In cell A11 I entered the formula, =IF(B1>0,A1,"")
In cell B11 I entered the formula, =IF(B1>0,B2,0)
I copied these formulas in the range A11:B15. I also based my chart series range off this new range.
Next I put the following VB code in:
Sub Hide_Rows()
Dim c As Range
For Each c In Worksheets("Sheet1").Range("A11:B15").Cells
If Application.Or(c.Value = 0, UCase(c.Value) = "#N/A") Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
End Sub
I assigned the macro a "click button".
Now when data is entered in the working range A1:B5 you click the button and it auto updates the chart and no #N/A's or 0's are displayed in the chart. What the macro does is hides the rows with either a 0 or #N/A.
I hope someone can use this.
Mike
My next issue was how do I do this in a bar chart?
Scenario:
My titles are in column A and values in coulmn B starting in cell A1.
Item A 8
Item B 0
Item C 9
Item D 0
Item E 5
If I make each item a series, I'll have five different series.
Now If I build a column chart it is going to display Item B and leave a blank between Item A and Item C. Here is my work around.
In cell A11 I entered the formula, =IF(B1>0,A1,"")
In cell B11 I entered the formula, =IF(B1>0,B2,0)
I copied these formulas in the range A11:B15. I also based my chart series range off this new range.
Next I put the following VB code in:
Sub Hide_Rows()
Dim c As Range
For Each c In Worksheets("Sheet1").Range("A11:B15").Cells
If Application.Or(c.Value = 0, UCase(c.Value) = "#N/A") Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
End Sub
I assigned the macro a "click button".
Now when data is entered in the working range A1:B5 you click the button and it auto updates the chart and no #N/A's or 0's are displayed in the chart. What the macro does is hides the rows with either a 0 or #N/A.
I hope someone can use this.
Mike
