Hide Zero Values

[PaulOakley]

Hello,

I need to run a macro to hide any rows that contain a zero value in a certain column number. e.g hide all rows that contain a zero value in column B?

Is this possible do you think?

Thanks in advance

Paul

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As rng

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "B").Value = 0 Then
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then rng.Hidden = True

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulOakley" <[email protected]> wrote
in message news:[email protected]...
>
> Hello,
>
> I need to run a macro to hide any rows that contain a zero value in a
> certain column number. e.g hide all rows that contain a zero value in
> column B?
>
> Is this possible do you think?
>
> Thanks in advance
>
> Paul
>
>
> --
> PaulOakley
> ------------------------------------------------------------------------
> PaulOakley's Profile:
http://www.excelforum.com/member.php...o&userid=25103
> View this thread: http://www.excelforum.com/showthread...hreadid=529130
>

[Ardus Petus]

Typo:

Dim rng as Range

HTH
--
AP

"Bob Phillips" <[email protected]> a écrit dans le message
de news:[email protected]...
> Sub Test()
> Dim iLastRow As Long
> Dim i As Long
> Dim rng As rng
>
> iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
> For i = 1 To iLastRow
> If Cells(i, "B").Value = 0 Then
> If rng Is Nothing Then
> Set rng = Rows(i)
> Else
> Set rng = Union(rng, Rows(i))
> End If
> End If
> Next i
>
> If Not rng Is Nothing Then rng.Hidden = True
>
> End Sub
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "PaulOakley" <[email protected]>
wrote
> in message news:[email protected]...
> >
> > Hello,
> >
> > I need to run a macro to hide any rows that contain a zero value in a
> > certain column number. e.g hide all rows that contain a zero value in
> > column B?
> >
> > Is this possible do you think?
> >
> > Thanks in advance
> >
> > Paul
> >
> >
> > --
> > PaulOakley
> > ------------------------------------------------------------------------
> > PaulOakley's Profile:
> http://www.excelforum.com/member.php...o&userid=25103
> > View this thread:
http://www.excelforum.com/showthread...hreadid=529130
> >
>
>

[PaulOakley]

Seems to be coming back with error msg of "user defined ype not defined"

Dim rng As rng- this part is being highlighted, is this right, or should i be entering data here

[PaulOakley]

If Not rng Is Nothing Then rng.Hidden = True

This is now showing as a debug error.. I know very little VBA, so dont seem to be able to change the error..

Thanks in advance

[Bob Phillips]

Thanks mate.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> Typo:
>
> Dim rng as Range
>
> HTH
> --
> AP
>
> "Bob Phillips" <[email protected]> a écrit dans le message
> de news:[email protected]...
> > Sub Test()
> > Dim iLastRow As Long
> > Dim i As Long
> > Dim rng As rng
> >
> > iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
> > For i = 1 To iLastRow
> > If Cells(i, "B").Value = 0 Then
> > If rng Is Nothing Then
> > Set rng = Rows(i)
> > Else
> > Set rng = Union(rng, Rows(i))
> > End If
> > End If
> > Next i
> >
> > If Not rng Is Nothing Then rng.Hidden = True
> >
> > End Sub
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "PaulOakley" <[email protected]>
> wrote
> > in message
news:[email protected]...
> > >
> > > Hello,
> > >
> > > I need to run a macro to hide any rows that contain a zero value in a
> > > certain column number. e.g hide all rows that contain a zero value in
> > > column B?
> > >
> > > Is this possible do you think?
> > >
> > > Thanks in advance
> > >
> > > Paul
> > >
> > >
> > > --
> > > PaulOakley
> >
> ------------------------------------------------------------------------
> > > PaulOakley's Profile:
> > http://www.excelforum.com/member.php...o&userid=25103
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=529130
> > >
> >
> >
>
>

[Bob Phillips]

Paul,

Ardus pointed out a typo, and I need a bit more property

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "B").Value = 0 Then
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulOakley" <[email protected]> wrote
in message news:[email protected]...
>
> If Not rng Is Nothing Then rng.Hidden = True
>
> This is now showing as a debug error.. I know very little VBA, so dont
> seem to be able to change the error..
>
> Thanks in advance
>
>
> --
> PaulOakley
> ------------------------------------------------------------------------
> PaulOakley's Profile:
http://www.excelforum.com/member.php...o&userid=25103
> View this thread: http://www.excelforum.com/showthread...hreadid=529130
>