Prev: How do I use an indirect reference for a chart scale in Excel?
Next: How do I sort a Spreadsheet?
From: jaimeo on 5 Mar 2010 17:04
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.
 | 
Pages: 1
Prev: How do I use an indirect reference for a chart scale in Excel?
Next: How do I sort a Spreadsheet?