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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|