Hide Zeros

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

See response in excel.misc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"PaulOakley" <PaulOakley.25omt0_1144057502.1492@excelforum-nospam.com> wrote
in message news:PaulOakley.25omt0_1144057502.1492@excelforum-nospam.com...
>
> 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=529131
>

Sub HideRowsWithZero()

Dim i As Long
Dim lCol As Long

'column to look for zero's
lCol = 1

Application.ScreenUpdating = False

For i = 1 To 100
If Cells(i, lCol) = 0 And _
Not IsEmpty(Cells(i, lCol)) Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next

Application.ScreenUpdating = True

End Sub


RBS


"PaulOakley" <PaulOakley.25omt0_1144057502.1492@excelforum-nospam.com> wrote
in message news:PaulOakley.25omt0_1144057502.1492@excelforum-nospam.com...
>
> 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=529131
>

This should do it:

Sub HideRows()
Dim LRow As Long
Dim MyRng As Range
Dim c As Range

LRow = Cells(Rows.Count, "B").End(xlUp).Row
Set MyRng = Range("B1:B" & LRow)

For Each c In MyRng
If c.Value = "0" Then
c.EntireRow.Hidden = True
End If
Next c
End Sub

Mike F

"PaulOakley" <PaulOakley.25omt0_1144057502.1492@excelforum-nospam.com> wrote
in message news:PaulOakley.25omt0_1144057502.1492@excelforum-nospam.com...
>
> 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=529131
>

I am having problems getting this to work.

I attach a file which perhaps will help you see my problems.

Thanks in advance

Paul

Try this one.
If it doesn't work explain what is wrong, rather using attachements.


Sub HideRowsWithZero()

Dim i As Long
Dim lCol As Long
Dim LR As Long

'last row of data
LR = Cells(65536, 1).End(xlUp).Row

'column to look for zero's, in this case column B
lCol = 2

Application.ScreenUpdating = False

For i = 1 To LR
If Cells(i, lCol) = 0 And _
Not IsEmpty(Cells(i, lCol)) Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next

Application.ScreenUpdating = True

End Sub


RBS


"PaulOakley" <PaulOakley.25orfn_1144063516.9904@excelforum-nospam.com> wrote
in message news:PaulOakley.25orfn_1144063516.9904@excelforum-nospam.com...
>
> I am having problems getting this to work.
>
> I attach a file which perhaps will help you see my problems.
>
> Thanks in advance
>
> Paul
>
>
> +-------------------------------------------------------------------+
> |Filename: Summary.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4570 |
> +-------------------------------------------------------------------+
>
> --
> PaulOakley
> ------------------------------------------------------------------------
> PaulOakley's Profile:
> http://www.excelforum.com/member.php...o&userid=25103
> View this thread: http://www.excelforum.com/showthread...hreadid=529131
>

Apoligies, thought i it might have been the easiest way to show a problem.

Let me explain the problem further,

I have changed the style of my sheet to try and accom the macro,

I want the macro to:

Check line 25 for any zero values, if a zero value occurs to hide the column that corresponds with that row, which could be any column from B-indefinate.

Does this explain the dilemma any further?

> that corresponds with that row ??

You must try to explain a bit more clearly.

RBS

"PaulOakley" <PaulOakley.25osla_1144065001.9538@excelforum-nospam.com> wrote
in message news:PaulOakley.25osla_1144065001.9538@excelforum-nospam.com...
>
> Apoligies, thought i it might have been the easiest way to show a
> problem.
>
> Let me explain the problem further,
>
> I have changed the style of my sheet to try and accom the macro,
>
> I want the macro to:
>
> Check line 25 for any zero values, if a zero value occurs to hide the
> column that corresponds with that row, which could be any column from
> B-indefinate.
>
> Does this explain the dilemma any further?
>
>
> --
> PaulOakley
> ------------------------------------------------------------------------
> PaulOakley's Profile:
> http://www.excelforum.com/member.php...o&userid=25103
> View this thread: http://www.excelforum.com/showthread...hreadid=529131
>

Ok, for example if e25=0, it hides column E, if f25=0 it hides column F.....
if g25=1 it does not hide...

Well, it will be just the same, except you will swap rows and columns like
this:

Sub HideColumnsWithZero()

Dim i As Long
Dim lRow As Long
Dim LC As Long

'last Column of data
LC = Cells(256).End(xlToLeft).Column

'row to look for zero's, in this case row 25
lRow = 25

Application.ScreenUpdating = False

For i = 1 To LC
If Cells(lRow, i) = 0 And _
Not IsEmpty(Cells(lRow, i)) Then
Cells(i).EntireColumn.Hidden = True
End If
Next

Application.ScreenUpdating = True

End Sub


RBS


"PaulOakley" <PaulOakley.25ovlm_1144068904.3549@excelforum-nospam.com> wrote
in message news:PaulOakley.25ovlm_1144068904.3549@excelforum-nospam.com...
>
> Ok, for example if e25=0, it hides column E, if f25=0 it hides column
> F.....
> if g25=1 it does not hide...
>
>
> --
> PaulOakley
> ------------------------------------------------------------------------
> PaulOakley's Profile:
> http://www.excelforum.com/member.php...o&userid=25103
> View this thread: http://www.excelforum.com/showthread...hreadid=529131
>