Hiding rows:hide and unhide if any number higher than 0 is entered

is there a way to hide rows if there is a zero in certain cells.
ex
A
1 12
2 0
3 11
4 3
5 0

rows 2 & 5 would hide and unhide if any number higher than 0 is entered
later for those cells. The cells in column A are linked to another worksheet.

Hi not bright

You could try this:

Right-click the tab of your sheet, click View Code and paste:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A2:A" &
Cells.SpecialCells(xlCellTypeLastCell).Row) 'best method?
If c.Value = 0 Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next
Application.ScreenUpdating = True
End Sub

I'm not sure if the range finder is the best for you (it finds the last
row used in the entire sheet) and you may be better off substituting

Range("A2:A" & Cells.SpecialCells(xlCellTypeLastCell).Row)

with something like

Range("A2:A100") for whatever your actual range mght be.

Regards

Steve

I've used things like this before, but don't really like it because once
it's hidden you have to manually unhide the rows to change the value. On
issues like this I generally try for a one-time solution. A change event
doesn't give you that flexibility.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


<britwiz@hotmail.com> wrote in message
news:1144941781.691127.269240@e56g2000cwe.googlegroups.com...
> Hi not bright
>
> You could try this:
>
> Right-click the tab of your sheet, click View Code and paste:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim c As Range
> Application.ScreenUpdating = False
> For Each c In Range("A2:A" &
> Cells.SpecialCells(xlCellTypeLastCell).Row) 'best method?
> If c.Value = 0 Then
> c.EntireRow.Hidden = True
> Else
> c.EntireRow.Hidden = False
> End If
> Next
> Application.ScreenUpdating = True
> End Sub
>
> I'm not sure if the range finder is the best for you (it finds the last
> row used in the entire sheet) and you may be better off substituting
>
> Range("A2:A" & Cells.SpecialCells(xlCellTypeLastCell).Row)
>
> with something like
>
> Range("A2:A100") for whatever your actual range mght be.
>
> Regards
>
> Steve
>

If the row is hidden when a value in the row is zero, how would you change
the zero value, which is hidden? With a macro, or is it a formula depending
on data outside the hidden rows?

"not bright" wrote:

> is there a way to hide rows if there is a zero in certain cells.
> ex
> A
> 1 12
> 2 0
> 3 11
> 4 3
> 5 0
>
> rows 2 & 5 would hide and unhide if any number higher than 0 is entered
> later for those cells. The cells in column A are linked to another worksheet.

example:
sheet3 c6 has =Sheet1!C16. Sheet1 C16 has nothing in it
Sheet3 Row6 would be hidden
If add a number to Sheet1 C16
Sheet3 Row6 would unhide

Is this possible


"Patricia Shannon" wrote:

> If the row is hidden when a value in the row is zero, how would you change
> the zero value, which is hidden? With a macro, or is it a formula depending
> on data outside the hidden rows?
>
> "not bright" wrote:
>
> > is there a way to hide rows if there is a zero in certain cells.
> > ex
> > A
> > 1 12
> > 2 0
> > 3 11
> > 4 3
> > 5 0
> >
> > rows 2 & 5 would hide and unhide if any number higher than 0 is entered
> > later for those cells. The cells in column A are linked to another worksheet.

I hoped it might be done with filters, but of course, if it were that easy,
someone with more knowledge than I would have already suggested that.
Since the data that is changing is on a different sheet than the place you
want to hide rows, you could do write a Worksheet_Activate macro for the
worksheet you want to hide/unhide the rows on.

"not bright" wrote:

> example:
> sheet3 c6 has =Sheet1!C16. Sheet1 C16 has nothing in it
> Sheet3 Row6 would be hidden
> If add a number to Sheet1 C16
> Sheet3 Row6 would unhide
>
> Is this possible
>
>
> "Patricia Shannon" wrote:
>
> > If the row is hidden when a value in the row is zero, how would you change
> > the zero value, which is hidden? With a macro, or is it a formula depending
> > on data outside the hidden rows?
> >
> > "not bright" wrote:
> >
> > > is there a way to hide rows if there is a zero in certain cells.
> > > ex
> > > A
> > > 1 12
> > > 2 0
> > > 3 11
> > > 4 3
> > > 5 0
> > >
> > > rows 2 & 5 would hide and unhide if any number higher than 0 is entered
> > > later for those cells. The cells in column A are linked to another worksheet.