automate hide row
 

Is there a formula i can set to automate hiding a row when a
particular cell returns a zero value?

automate hide row
 

If you are gonna be sure that the cell value will be a constant (not
formula) then, use the following procedure in the given sheet module,
and save the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value = 0 Then
Target.EntireRow.Hidden = True
End If
End If
End Sub

You may try using the following module if the result 0 will be due to
formula, and not constant.

Private Sub Worksheet_Calculate()
For i = 1 To ThisWorkbook.Sheets(Sheet1).UsedRange.Rows.Count
If Cells(i, 1).Value = 0 Then
Cells(i, 1).EntireRow.Hidden = True
Else
Cells(i, 1).EntireRow.Hidden = False
End If
Next i
End Sub

I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, & other resources
Please visit http://socko.wordpress.com/

automate hide row
 

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H:H" '<=== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

Me.Rows(.Row).Hidden = .Value = 0
End With
End If
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

"Nicole" wrote in message
...
Is there a formula i can set to automate hiding a row when a
particular cell returns a zero value?

automate hide row
 

Hi Bob,

I have paste the below string into the view code page, nothing
happens, should i click something after pasting and close the view
code page? Let me show a simple example:
Fruit Jan Feb Mar
A 1 1 1
B 2 3 1
C 0 0 0
D 1 1 1

Altogether I have col A to K and row 3 to 400, i want to hide row like
C above automatically

Nicole
On Dec 18, 4:52*pm, "Bob Phillips" wrote:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H:H" '<=== change to suit

* * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

* * * * With Target

* * * * * * Me.Rows(.Row).Hidden = .Value = 0
* * * * End With
* * End If
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

"Nicole" wrote in message

...



Is there a formula i can set toautomatehiding arowwhen a
particular cell returns a zero value?-Hidequoted text -

- Show quoted text -

automate hide row
 

On Dec 18, 4:51*pm, Socko wrote:
If you are gonna be sure that the cell value will be a constant (not
formula) then, use the following procedure in the given sheet module,
and save the workbook.

* * * * Private Sub Worksheet_Change(ByVal Target As Range)
* * * * If Target.Column = 1 Then
* * * * * If Target.Value = 0 Then
* * * * * * Target.EntireRow.Hidden = True
* * * * * End If
* * * * End If
* * * * End Sub

You may try using the following module if the result 0 will be due to
formula, and not constant.

* * * * Private Sub Worksheet_Calculate()
* * * * For i = 1 To ThisWorkbook.Sheets(Sheet1).UsedRange.Rows..Count
* * * * * If Cells(i, 1).Value = 0 Then
* * * * * * Cells(i, 1).EntireRow.Hidden = True
* * * * * Else
* * * * * * Cells(i, 1).EntireRow.Hidden = False
* * * * * End If
* * * * Next i
* * * * End Sub

I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, & other resources
Please visithttp://socko.wordpress.com/

Hi Socko,
I have copied and paste the Calculate section into the view code and
save, nothing seem to happen, those rows with the blank cell on column
P still there.

automate hide row
 

My code is designed to run when you enter the value. So you would change H:H
to B:K and then set a value.

--
__________________________________
HTH

Bob

"Nicole" wrote in message
...
Hi Bob,

I have paste the below string into the view code page, nothing
happens, should i click something after pasting and close the view
code page? Let me show a simple example:
Fruit Jan Feb Mar
A 1 1 1
B 2 3 1
C 0 0 0
D 1 1 1

Altogether I have col A to K and row 3 to 400, i want to hide row like
C above automatically

Nicole
On Dec 18, 4:52 pm, "Bob Phillips" wrote:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H:H" '<=== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

Me.Rows(.Row).Hidden = .Value = 0
End With
End If
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

"Nicole" wrote in message

...



Is there a formula i can set toautomatehiding arowwhen a
particular cell returns a zero value?-Hidequoted text -

- Show quoted text -

automate hide row
 

On Dec 19, 4:30*am, "Bob Phillips" wrote:
My code is designed to run when you enter the value. So you would change H:H
to B:K and then set a value.

--
__________________________________
HTH

Bob

"Nicole" wrote in message

...
Hi Bob,

I have paste the below string into the view code page, nothing
happens, should i click something after pasting and close the view
code page? Let me show a simple example:
Fruit *Jan Feb Mar
A * * * *1 * *1 * *1
B * * * *2 * 3 * * 1
C * * * *0 * *0 * *0
D * * * *1 * *1 * *1

Altogether I have col A to K and row 3 to 400, i want tohiderow like
C above automatically

Nicole
On Dec 18, 4:52 pm, "Bob Phillips" wrote:



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H:H" '<=== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

Me.Rows(.Row).Hidden = .Value = 0
End With
End If
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

"Nicole" wrote in message

....

Is there a formula i can set toautomatehiding arowwhen a
particular cell returns a zero value?-Hidequoted text -

- Show quoted text --Hidequoted text -

- Show quoted text -

Thanks, my problem is resolved.