Automate Hide/Unhide Rows
 

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

Thank you

Automate Hide/Unhide Rows
 

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

On Tue, 27 Oct 2009 09:33:04 -0700, Jeremy
wrote:

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

Thank you

Automate Hide/Unhide Rows
 

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


"Gord Dibben" wrote:

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

On Tue, 27 Oct 2009 09:33:04 -0700, Jeremy
wrote:

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

Thank you

.

Automate Hide/Unhide Rows
 

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.


Jeremy wrote:

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

"Gord Dibben" wrote:

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

On Tue, 27 Oct 2009 09:33:04 -0700, Jeremy
wrote:

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

Thank you

.


--

Dave Peterson