Automate Hide/Unhide Rows

[Jeremy]

Is there a way to automate hiding of rows where if A1>0 Rows 1 and 2 will be
hidden and if A1<1 Rows 1 and 2 will show?

Thank you

 

[Gord Dibben]

You can, using VBA, hide and unhide rows based upon the value in A1

How does A1 get its value? Must be a calculated return.

Your parameters look faulty to me.

0 can be < 1 and/or < 1 can be > 0

Gord Dibben MS Excel MVP

 

[Jeremy]

Below is the what I am trying to use. E38 realy = to another set of cells.
If I type in a number in E38 it works but if I put in a formula to have a
positive or no number it does not.


Private Sub Worksheet_Change(ByVal Target As Range)
'Application.EnableEvents = False' not necessary
If Not Application.Intersect(Target, Range("E38")) Is Nothing Then
Rows("36:37").Hidden = IIf(UCase(Range("E38")) <= 0, True, False) 'spelling
End If
'Application.EnableEvents = True

 

[Dave Peterson]

If E38 contains a formula and changes because excel recalculated, then you'll
have to use the Worksheet_Calculate event.

Option Explicit
Private Sub Worksheet_Calculate()

'depending on your version of excel, this may be necessary!
Application.EnableEvents = False ' not necessary

Me.Rows("36:37").Hidden = CBool(Me.Range("E38").Value <= 0)

Application.EnableEvents = True

End Sub

xl2003 added some parameters to the =subtotal() function (=subtotal(1##,...).
The 100 level parms means that it will ignore rows hidden manually (not just by
autofilter) in the subtotal.

So hiding rows can cause excel to recalc.