if cell = 0 then hide the row?

Here is one way which assumes headings in rows 1 and 2. Just chan ge the F3
to the first real data row.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "F3:F88536"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = 0 Then
.EntireRow.Hidden = True
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Al" <Al@discussions.microsoft.com> wrote in message
news:85E36FBE-5FB3-4A33-912D-B1ACC3E5788D@microsoft.com...
> My spreadsheet is a list of names and hours and charges, but if name X,
say
> D14, has zero hours, say F14 = 0, then I do not need the row - otherwise
my
> spreadsheet will have so many unused rows - and so want to hide it.
>
> To complicate matters, my spreadsheet has titles, etc, and clearly such a
> row has no value for hours in any cell, and these rows are not to be
hidden.
>
> I see something that only conisders the "data rows" and hides them if the
> hours cell, F14, is zero.
>
> Allan
>

Al,

If you don't want to do it with a macro, you can achieve this using a
filter. In a column that you don't use (say column M), enter the
formula = F3 in cell M3 and copy down to cover your rows of data.
Highlight this column and select Data | Filter | Autofilter. Then
select Custom... on the pull-down list and specify "Not Equal to" and
"0" (i.e. zero) in the panels. All your rows with zero in column F will
be hidden.

Pete

Problem with that is it needs to be repeated every time a value is changed
to 0, it is not event driven.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pete" <pashurst@auditel.net> wrote in message
news:1134388888.499724.56670@g47g2000cwa.googlegroups.com...
> Al,
>
> If you don't want to do it with a macro, you can achieve this using a
> filter. In a column that you don't use (say column M), enter the
> formula = F3 in cell M3 and copy down to cover your rows of data.
> Highlight this column and select Data | Filter | Autofilter. Then
> select Custom... on the pull-down list and specify "Not Equal to" and
> "0" (i.e. zero) in the panels. All your rows with zero in column F will
> be hidden.
>
> Pete
>

Typo, should be

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "F3:F66536"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = 0 Then
.EntireRow.Hidden = True
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:eSvXluv$FHA.216@TK2MSFTNGP15.phx.gbl...
> Here is one way which assumes headings in rows 1 and 2. Just chan ge the
F3
> to the first real data row.
>
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "F3:F88536"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> If .Value = 0 Then
> .EntireRow.Hidden = True
> End If
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Al" <Al@discussions.microsoft.com> wrote in message
> news:85E36FBE-5FB3-4A33-912D-B1ACC3E5788D@microsoft.com...
> > My spreadsheet is a list of names and hours and charges, but if name X,
> say
> > D14, has zero hours, say F14 = 0, then I do not need the row -
otherwise
> my
> > spreadsheet will have so many unused rows - and so want to hide it.
> >
> > To complicate matters, my spreadsheet has titles, etc, and clearly such
a
> > row has no value for hours in any cell, and these rows are not to be
> hidden.
> >
> > I see something that only conisders the "data rows" and hides them if
the
> > hours cell, F14, is zero.
> >
> > Allan
> >
>
>

Bob, Pete,

Thanks for your replies...allow me to digest your advise then I'll get back
to you with my results! One comment, I have now though, is that my "titles"
do not only apprear in say rows 1 & 2. I have many "categories" of personnel,
thus rows 17& 18 might be a header for "management", rows 34&35, for "sales
reps", rows 51&52 for "mechanics" etc etc. Does this have a consequence on
your replies?

"Bob Phillips" wrote:

> Typo, should be
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "F3:F66536"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> If .Value = 0 Then
> .EntireRow.Hidden = True
> End If
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:eSvXluv$FHA.216@TK2MSFTNGP15.phx.gbl...
> > Here is one way which assumes headings in rows 1 and 2. Just chan ge the
> F3
> > to the first real data row.
> >
> >
> > Option Explicit
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Const WS_RANGE As String = "F3:F88536"
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > With Target
> > If .Value = 0 Then
> > .EntireRow.Hidden = True
> > End If
> > End With
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Al" <Al@discussions.microsoft.com> wrote in message
> > news:85E36FBE-5FB3-4A33-912D-B1ACC3E5788D@microsoft.com...
> > > My spreadsheet is a list of names and hours and charges, but if name X,
> > say
> > > D14, has zero hours, say F14 = 0, then I do not need the row -
> otherwise
> > my
> > > spreadsheet will have so many unused rows - and so want to hide it.
> > >
> > > To complicate matters, my spreadsheet has titles, etc, and clearly such
> a
> > > row has no value for hours in any cell, and these rows are not to be
> > hidden.
> > >
> > > I see something that only conisders the "data rows" and hides them if
> the
> > > hours cell, F14, is zero.
> > >
> > > Allan
> > >
> >
> >
>
>
>

Then you need some type of indicator, such as a cell value, that can be
tested to see if it is a heading row.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Al" <Al@discussions.microsoft.com> wrote in message
news:8029713D-02BD-4081-9696-202D40DD59BD@microsoft.com...
> Bob, Pete,
>
> Thanks for your replies...allow me to digest your advise then I'll get
back
> to you with my results! One comment, I have now though, is that my
"titles"
> do not only apprear in say rows 1 & 2. I have many "categories" of
personnel,
> thus rows 17& 18 might be a header for "management", rows 34&35, for
"sales
> reps", rows 51&52 for "mechanics" etc etc. Does this have a consequence
on
> your replies?
>
> "Bob Phillips" wrote:
>
> > Typo, should be
> >
> > Option Explicit
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Const WS_RANGE As String = "F3:F66536"
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > With Target
> > If .Value = 0 Then
> > .EntireRow.Hidden = True
> > End If
> > End With
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:eSvXluv$FHA.216@TK2MSFTNGP15.phx.gbl...
> > > Here is one way which assumes headings in rows 1 and 2. Just chan ge
the
> > F3
> > > to the first real data row.
> > >
> > >
> > > Option Explicit
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Const WS_RANGE As String = "F3:F88536"
> > >
> > > On Error GoTo ws_exit:
> > > Application.EnableEvents = False
> > > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > > With Target
> > > If .Value = 0 Then
> > > .EntireRow.Hidden = True
> > > End If
> > > End With
> > > End If
> > >
> > > ws_exit:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > 'This is worksheet event code, which means that it needs to be
> > > 'placed in the appropriate worksheet code module, not a standard
> > > 'code module. To do this, right-click on the sheet tab, select
> > > 'the View Code option from the menu, and paste the code in.
> > >
> > >
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Al" <Al@discussions.microsoft.com> wrote in message
> > > news:85E36FBE-5FB3-4A33-912D-B1ACC3E5788D@microsoft.com...
> > > > My spreadsheet is a list of names and hours and charges, but if name
X,
> > > say
> > > > D14, has zero hours, say F14 = 0, then I do not need the row -
> > otherwise
> > > my
> > > > spreadsheet will have so many unused rows - and so want to hide it.
> > > >
> > > > To complicate matters, my spreadsheet has titles, etc, and clearly
such
> > a
> > > > row has no value for hours in any cell, and these rows are not to be
> > > hidden.
> > > >
> > > > I see something that only conisders the "data rows" and hides them
if
> > the
> > > > hours cell, F14, is zero.
> > > >
> > > > Allan
> > > >
> > >
> > >
> >
> >
> >

If you want to try it my way then you will need to delete the formula
from the rows which are headings so that the cells are blank rather
than zero.

Pete

OK..now I'm off to see if I can get this to work. Thanks for all advice to
date!

"Pete" wrote:

> If you want to try it my way then you will need to delete the formula
> from the rows which are headings so that the cells are blank rather
> than zero.
>
> Pete
>
>

OK..now I'm off to see if I can get this to work. Thanks for all advice to
date!

"Bob Phillips" wrote:

> Then you need some type of indicator, such as a cell value, that can be
> tested to see if it is a heading row.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Al" <Al@discussions.microsoft.com> wrote in message
> news:8029713D-02BD-4081-9696-202D40DD59BD@microsoft.com...
> > Bob, Pete,
> >
> > Thanks for your replies...allow me to digest your advise then I'll get
> back
> > to you with my results! One comment, I have now though, is that my
> "titles"
> > do not only apprear in say rows 1 & 2. I have many "categories" of
> personnel,
> > thus rows 17& 18 might be a header for "management", rows 34&35, for
> "sales
> > reps", rows 51&52 for "mechanics" etc etc. Does this have a consequence
> on
> > your replies?
> >
> > "Bob Phillips" wrote:
> >
> > > Typo, should be
> > >
> > > Option Explicit
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Const WS_RANGE As String = "F3:F66536"
> > >
> > > On Error GoTo ws_exit:
> > > Application.EnableEvents = False
> > > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > > With Target
> > > If .Value = 0 Then
> > > .EntireRow.Hidden = True
> > > End If
> > > End With
> > > End If
> > >
> > > ws_exit:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:eSvXluv$FHA.216@TK2MSFTNGP15.phx.gbl...
> > > > Here is one way which assumes headings in rows 1 and 2. Just chan ge
> the
> > > F3
> > > > to the first real data row.
> > > >
> > > >
> > > > Option Explicit
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > Const WS_RANGE As String = "F3:F88536"
> > > >
> > > > On Error GoTo ws_exit:
> > > > Application.EnableEvents = False
> > > > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > > > With Target
> > > > If .Value = 0 Then
> > > > .EntireRow.Hidden = True
> > > > End If
> > > > End With
> > > > End If
> > > >
> > > > ws_exit:
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > > 'This is worksheet event code, which means that it needs to be
> > > > 'placed in the appropriate worksheet code module, not a standard
> > > > 'code module. To do this, right-click on the sheet tab, select
> > > > 'the View Code option from the menu, and paste the code in.
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "Al" <Al@discussions.microsoft.com> wrote in message
> > > > news:85E36FBE-5FB3-4A33-912D-B1ACC3E5788D@microsoft.com...
> > > > > My spreadsheet is a list of names and hours and charges, but if name
> X,
> > > > say
> > > > > D14, has zero hours, say F14 = 0, then I do not need the row -
> > > otherwise
> > > > my
> > > > > spreadsheet will have so many unused rows - and so want to hide it.
> > > > >
> > > > > To complicate matters, my spreadsheet has titles, etc, and clearly
> such
> > > a
> > > > > row has no value for hours in any cell, and these rows are not to be
> > > > hidden.
> > > > >
> > > > > I see something that only conisders the "data rows" and hides them
> if
> > > the
> > > > > hours cell, F14, is zero.
> > > > >
> > > > > Allan
> > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>