Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
How to Hide and Unhide Rows | Excel Discussion (Misc queries) | |||
Hide / Unhide columns and rows | Excel Discussion (Misc queries) | |||
Hide / UnHide - is there a Function to automate this ? | Excel Worksheet Functions | |||
Hide Unhide Rows | Excel Discussion (Misc queries) |