Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unhide row formula
I am using the following code to hide or unhide rows. How do I get this
formula to perform the activecell functions only when unhide is being performed? 'Application.EnableEvents = False' not necessary If Not Application.Intersect(Target, Range("B9")) Is Nothing Then Rows("10:11").Hidden = IIf(UCase(Range("B9")) = "", True, False) 'spelling 'ActiveCell.Offset(-6, 0).Select ActiveCell.End(xlUp).Select ActiveCell.Offset(1, 0).Select End If 'Application.EnableEvents = False |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unhide row formula
Drop the IIF stuff and use a regular if/then/else structure.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'single cell at a time?? If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("b9")) Is Nothing Then Exit Sub End If Application.EnableEvents = False If Target.Value = "" Then Me.Rows("10:11").Hidden = True Else Me.Rows("10:11").Hidden = False Target.End(xlUp).Offset(1, 0).Select End If Application.EnableEvents = True End Sub I used the worksheet_change event. I'm not sure what event you're using. ps. the last application.enableevents should be true in your original code. And with some versions of excel (xl2003 and higher), hiding/unhiding rows will cause the worksheet to recalculate--and disabling events may be wanted. And if you're selecting a range, you may want to stop the worksheet_Selectionchange from firing, too. It seems like a good thing to keep both those .enableevents lines in your code. Jeremy wrote: I am using the following code to hide or unhide rows. How do I get this formula to perform the activecell functions only when unhide is being performed? 'Application.EnableEvents = False' not necessary If Not Application.Intersect(Target, Range("B9")) Is Nothing Then Rows("10:11").Hidden = IIf(UCase(Range("B9")) = "", True, False) 'spelling 'ActiveCell.Offset(-6, 0).Select ActiveCell.End(xlUp).Select ActiveCell.Offset(1, 0).Select End If 'Application.EnableEvents = False -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I unhide the formula bar | Excel Discussion (Misc queries) | |||
unhide row does not unhide the hidden rows | Excel Worksheet Functions | |||
How do I unhide the formula bar? | Excel Discussion (Misc queries) | |||
how do i unhide a worksheet in excel 2003? unhide tab don't work | Excel Discussion (Misc queries) | |||
How do I unhide a column when format/column/unhide won't work | Excel Worksheet Functions |