Thread: Hide whole row when cel C3 is 0 (zero)

Hello,
Who can help me with this next problem?

I want to hide an whole row when the formula in row C give the anser
"0"
The other row are filled!

Who can give me the code for this?

Greets Berry

Hi Berry

Rightclick the sheet tab, "view code", paste this in:

Private Sub Worksheet_Calculate()
If Me.Range("C3").Value = 0 Then
Me.Columns(3).Hidden = True
Else
Me.Columns(3).Hidden = False
End If
End Sub

HTH. Best wishes Harald

<blommerse@saz.nl> skrev i melding
news:1150095682.955357.169390@c74g2000cwc.googlegroups.com...
> Hello,
> Who can help me with this next problem?
>
> I want to hide an whole row when the formula in row C give the anser
> "0"
> The other row are filled!
>
> Who can give me the code for this?
>
> Greets Berry
>

Actually this is how the sheet looks:

Colum C Colum D

T T0601CFI
C C0651CFI
H H0602CFI
0 0
0 0

Now I want to automaticly hide the rows with a zero in colum C and also
the ones with a "C"
Who can help me with this code??

Greets

Hi Herald,

That didn't worked. Nothing happened when I copied the code!

Do you need more info??
Greets

Hi Berry,

You can try this:

Private Sub Worksheet_Calculate()
Dim lRowLoop As Long

For lRowLoop = 1 To UsedRange.Rows.Count
If Cells(lRowLoop, 3).Value = 0 Then
Cells(lRowLoop, 3).EntireRow.Hidden = True
ElseIf Cells(lRowLoop, 3).Value = "C" Then
Cells(lRowLoop, 3).EntireRow.Hidden = True
Else
Cells(lRowLoop, 3).EntireRow.Hidden = False
End If
Next
End Sub

This code will be executed each time the sheet is recalculated.


HTH,


Executor

Berry wrote:
> Actually this is how the sheet looks:
>
> Colum C Colum D
>
> T T0601CFI
> C C0651CFI
> H H0602CFI
> 0 0
> 0 0
>
> Now I want to automaticly hide the rows with a zero in colum C and also
> the ones with a "C"
> Who can help me with this code??
>
> Greets

Hi Wouter,

It is almost working, but now when the sheet is calculating my screen
start tripping, when I Debug the code the sheet mark End if yellow.
Do you know what''s wrong.

Maybe I can send you the file.

Greets

My mistake. I was hiding the C colomn, not row 3. Those things occur when I
post before drinking my first coffee. But something should happen, not
nothing. Ok, try this then

Dim X As Boolean

Private Sub Worksheet_Calculate()
Dim R As Long
If X = True Then Exit Sub
X = True
For R = 1 To Cells(65535, 3).End(xlUp).Row
Select Case Cells(R, 3).Value
Case "", 0, "C"
Me.Rows(R).Hidden = True
Case Else
Me.Rows(R).Hidden = False
End Select
Next
X = False
End Sub

HTH. Best wishes Harald


"Berry" <blommerse@saz.nl> skrev i melding
news:1150098249.748426.149230@j55g2000cwa.googlegroups.com...
>
> Hi Herald,
>
> That didn't worked. Nothing happened when I copied the code!
>
> Do you need more info??
> Greets
>

Thanks. This one work fine. Enjoy you coffee.

Cheers

"Berry" <blommerse@saz.nl> skrev i melding
news:1150106212.013477.95790@f6g2000cwb.googlegroups.com...
>
> Thanks. This one work fine. Enjoy you coffee.
>
> Cheers

Good. Cheers.
Best wishes Harald

Hi Berry,

I tested it without a C in any of the cells.


Try this:

Private Sub Worksheet_Calculate()
Dim lRowLoop As Long
Application.EnableEvents = False
For lRowLoop = 1 To UsedRange.Rows.Count
If Cells(lRowLoop, 3).Value = 0 Then
Cells(lRowLoop, 3).EntireRow.Hidden = True
ElseIf Cells(lRowLoop, 3).Value = "C" Then
Cells(lRowLoop, 3).EntireRow.Hidden = True
Else
Cells(lRowLoop, 3).EntireRow.Hidden = False
End If
Next
Application.EnableEvents = True
End Sub

HTH,

Executor.

Berry wrote:
> Hi Wouter,
>
> It is almost working, but now when the sheet is calculating my screen
> start tripping, when I Debug the code the sheet mark End if yellow.
> Do you know what''s wrong.
>
> Maybe I can send you the file.
>
> Greets

Hello,

The code is working correct, only 1 problem: There is a auto filter in
de row, when I filter something, It wont work.
Does anyone know what is wrong and what I can do about it.

Thanx for your reply's