Is it possible to do conditional column/row hiding?

  • Thread starter Thread starter hce
  • Start date Start date
H

hce

Hi Everyone

I'm really hopeless in excel... posting one question after another...

One final question... is it possible to run a macro to hid
rows/columns conditional upon a cell's value? For example, if the valu
in a3 and a4 is 0, the row would be hidden...

Cheer
 
One way:

Public Sub ConditionalHideRows()
Dim rCell As Range
Dim bHide As Boolean
For Each rCell In Range("A3:A4")
With rCell
If Not IsEmpty(.Value) Then _
If IsNumeric(.Value) Then _
bHide = (.Value = 0)
.EntireRow.Hidden = bHide
bHide = False
End With
Next rCell
End Sub
 
It is not clear which row you want to hide as you test 3 and 4, so I have
picked the active row

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A3,A4")) Is Nothing Then
If Range("A3").Value = 0 And Range("A4").Value = 0 Then
Target.EntireRow.Hidden = True
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

This code goes into the worksheet code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top