Conditionally hide rows

Any suggestions on how to conditionally hide rows on a worksheet? If a cell
range in a row has a non-zero sum quantity I want to display the entire row
and hide all rows that have zero quantity sum in the cell range of that row.

Hi Marc,

Try:
'=================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Range
Dim i As Long
Const StartCol As String = "B" '<<==== CHANGE
Const EndCol As String = "I" '<<==== CHANGE

If Not Intersect(Target, Columns(StartCol & _
":" & EndCol)) Is Nothing Then
For Each rw In Target.Rows
i = rw.Row
Rows(i).Hidden = Application.Sum(Range(Cells _
(i, StartCol), Cells(i, EndCol))) = 0
Next rw
End If

End Sub
'<<================

Cgange the StartCol and EndCol values to suit.
..
This is worksheet event code and should be pasted into the worksheets's
code module (not a standard module and not the workbook's ThisWorkbook
module):

*********************************
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
*********************************


---
Regards,
Norman



"Marc Kovner" <Marc [email protected]> wrote in message
news:[email protected]...
> Any suggestions on how to conditionally hide rows on a worksheet? If a
> cell
> range in a row has a non-zero sum quantity I want to display the entire
> row
> and hide all rows that have zero quantity sum in the cell range of that
> row.

Hi Marc,

To allow for formula and non-formula changes, better would be:

'=================>>
Private Sub Worksheet_Calculate()
Dim rw As Range
Dim i As Long
Dim rng As Range

Set rng = Range("B2:I20") '<<==== CHANGE

For Each rw In rng.Rows
i = rw.Row
Rows(i).Hidden = Application.Sum(rw) = 0
Next rw

End Sub
'<<=================

'=================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rw As Range
Dim i As Long

Set rng = Range("B2:I20") '<<==== CHANGE'

If Not Intersect(Target, rng) Is Nothing Then
For Each rw In Target.Rows
i = rw.Row
Rows(i).Hidden = Application.Sum(rw) = 0
Next rw
End If

End Sub
'<<=================

Change the address of the rng variable to suit.

Again, these are event procedures and should be pasted into the code module
behind the worksheet.


---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:e%[email protected]...
> Hi Marc,
>
> Try:
> '=================>>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rw As Range
> Dim i As Long
> Const StartCol As String = "B" '<<==== CHANGE
> Const EndCol As String = "I" '<<==== CHANGE
>
> If Not Intersect(Target, Columns(StartCol & _
> ":" & EndCol)) Is Nothing Then
> For Each rw In Target.Rows
> i = rw.Row
> Rows(i).Hidden = Application.Sum(Range(Cells _
> (i, StartCol), Cells(i, EndCol))) = 0
> Next rw
> End If
>
> End Sub
> '<<================
>
> Cgange the StartCol and EndCol values to suit.
> .
> This is worksheet event code and should be pasted into the worksheets's
> code module (not a standard module and not the workbook's ThisWorkbook
> module):
>
> *********************************
> Right-click the worksheet's tab
>
> Select 'View Code' from the menu and paste the code.
>
> Alt-F11 to return to Excel.
> *********************************
>
>
> ---
> Regards,
> Norman
>
>
>
> "Marc Kovner" <Marc [email protected]> wrote in message
> news:[email protected]...
>> Any suggestions on how to conditionally hide rows on a worksheet? If a
>> cell
>> range in a row has a non-zero sum quantity I want to display the entire
>> row
>> and hide all rows that have zero quantity sum in the cell range of that
>> row.
>
>