I can get line chart to ignore zeros but the legend still contains

  • Thread starter Thread starter jaimeo
  • Start date Start date
J

jaimeo

Hi,

I have a table linked to a series of worksheets. The table shows products 1
to 6 in cells A2:A8. It then shows sales for each year in b2, c2. c3 etc.
for product 1. b3, c3 etc for product two and so on.

It is designed to show products sold in lots of different countries. I have
a line chart linked to the table. It works fine if all six products are sold
in the country. If they are not then because Product 1-6 is always entered
even if not all products are sold the legend still shows them all. I have
tried NA'ing the cells which stops them being charted but the legend entry
remains. I tried returning a blank cell if the values for the series were
all 0 but the legend entry was still there, just blank but still assigned a
colour. I even tried deleting the data out of the cells entirely so they
were genuinely totally blank but the legend still picks it up.

I only need it to ignore a product if every value in the series of figures
for turnover is 0 (i.e. we do not sell it in that country). There are 120
possible charts that can be produced from the table so I am hoping to
automate it. Originally I thought maybe I should use the conditional
formatting posts I have seen around to do it. However, when I pass this work
to someone else there is a good chance they may decide they want the graph
colour design to have a different colour background thereby highlighting what
conditional formatting would have hidden.

What I am leaning towards now is a way to effectively say "if all values in
this series are 0 then ignore it entirely" with the option then that if in
the future we launch that product the chart then recognises the values are no
longer zero, I can rerun the code or whatever and it will prepare me the
correct chart.

Apologies for the long winded response. I am very much a VBA beginner and
even an Excel beginner but I am learning a lot as I dig into this.

Please advise if you can.
 
i think that the way i can do it is to use VBA to hide rows where b2:e2 (or
whatever the range of turnover figures is) all = 0?

That seems to work. Though it requires running a macro to do it I am
guessing I can record a macro of me applying that macro to a variety of
spaces on the sheet unless anyone knows how to adapt the following code so it
applies to rows 1 to 100 automatically? at the moment I seem to select a bit
at a time. it works but it would be easier not to have to do multiple runs
of the same macro.

thanks:

Option Explicit
Sub HideOnZero()
Dim rngTest As Range
Dim rngCell As Range
Dim lngStRow As Long
Dim lngEndRow As Long
Dim rngRow As Range
Dim blnZero As Boolean
Dim n As Integer

On Error GoTo ErrHnd

With ActiveSheet
'selected cell within required range
'set range to current region around selected cell
Set rngTest = ActiveCell.CurrentRegion
'get rows in range
lngStRow = rngTest.Rows(1).Row
lngEndRow = lngStRow + rngTest.Rows.Count - 1
'go through each row
For n = lngStRow To lngEndRow
blnZero = True
'test each cell in the row from column C to column H
For Each rngCell In Range("C" & Format(n, "#0") & ": H" & Format(n,
"#0")).Cells
'test if cell value is zero
If rngCell.Value <> 0 Then
blnZero = False
End If
'if a non-zero cell encountered don't test this row any more
If blnZero = False Then Exit For
'if we get to the last cell in this row (column H)
'then setup a range for the whole row
If rngCell.Column = Range("C" & Format(n, "#0") & ": H" & _
Format(n, "#0")).Columns.Count + 2 Then
Set rngRow = rngCell.EntireRow
End If
Next rngCell
'hide the row if no non-zero cells found
If blnZero = True Then
rngRow.Hidden = True
End If
Next n
End With
Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub
 
Can you not just use an autofilter to filter out the rows which are
blank? (based on an extra column which checks all the previous columns,
or a simple SUM of them perhaps).

If you do want to automate it based on something changing, or giving the
user a button for this, then the one line macro below should do the
trick - this filters the fourth column of your table, adjust the 4
according to your needs.


ActiveSheet.Range("$C$7:$F$13").AutoFilter Field:=4, Criteria1:="<>0",
Operator:=xlAnd


If your data can be brought across in a suitable layout then a
PivotChart might be a better option to ignore data that does not occur
in the source.

Hope this helps.
Adam
 
Back
Top