Unhide row formula

[Jeremy]

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]

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.