Hide Rows If Condition True

[wilbursj]

Hi
Is it possible to auto-hide a row if a condition is met. Ie If the value of
a specifc cell in that row is 0, i do not want the row to show, so either
hide it or make row hright minimal. If value changes, i would want it to
unhide or increase row height to make it visible.
Thanks

 

[Stefi]

You need a change event macro for that, something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ucell As Range, urows As Range
rownum = ActiveSheet.UsedRange.Rows.Count
Set urows = Range("C2:C" & ActiveSheet.UsedRange.Rows.Count) 'change
"C" to your specific column
For Each ucell In urows
ucell.EntireRow.Hidden = (ucell = 0)
Next ucell
End Sub

Regards,
Stefi


„wilbursj†ezt írta:

 

[Gord Dibben]

Depends upon how the quantity is derived which event type to use.

I will assume the value is formula-derived and in Column A

Private Sub Worksheet_Calculate()
'Hide rows with formulas that return 0
Dim cell As Range
On Error GoTo endit
Application.EnableEvents = False
Application.ScreenUpdating = False
With Me.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
If cell.Value = 0 Then cell.EntireRow.Hidden = True
Next cell
End With
endit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP