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
|