A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old March 5th, 2010, 09:54 PM posted to microsoft.public.excel.charting
jaimeo
external usenet poster
 
Posts: 3
Default I can get line chart to ignore zeros but the legend still contains

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.
  #2  
Old March 5th, 2010, 10:04 PM posted to microsoft.public.excel.charting
jaimeo
external usenet poster
 
Posts: 3
Default I can get line chart to ignore zeros but the legend still contains

I am using Excel 2007.

Thanks




"jaimeo" wrote:

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.

  #3  
Old March 5th, 2010, 11:04 PM posted to microsoft.public.excel.charting
jaimeo
external usenet poster
 
Posts: 3
Default I can get line chart to ignore zeros but the legend still cont

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

"jaimeo" wrote:

I am using Excel 2007.

Thanks




"jaimeo" wrote:

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.

  #4  
Old March 12th, 2010, 03:22 PM posted to microsoft.public.excel.charting
AdamV[_2_]
external usenet poster
 
Posts: 38
Default I can get line chart to ignore zeros but the legend still contains

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

On 05/03/2010 21:04, jaimeo wrote:
I am using Excel 2007.

Thanks




"jaimeo" wrote:

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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.