Clear Cell Content

Question

I have the VBA code in an Excel 2013 worksheet to hide two rows (B8:B9) when B7 selection is set to 'Pass'. That part works.

ClearContents closes Excel without any reason.

I want to clear those cell contents before/after the rows are hidden or unhidden.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("B7").Value <> "Pass" Then
        Range("B8:B9").ClearContents
        Rows("8:9").EntireRow.Hidden = True
    Else
        Rows("8:9").EntireRow.Hidden = False
    End If
End Sub

The B8:B9 cells are Data Validation List.

You need to set the target range or you will be in an infinite loop.`if target.address="$B$7" then` — Davesexcel, Aug 22 '18 at 16:45
Turn events off first - `Application.EnableEvents = False`, and then set it back to `True` at the end. — BigBen, Aug 22 '18 at 16:48
Possible duplicate of [Why MS Excel crashes and closes during worksheet change sub procedure](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure) — BigBen, Aug 22 '18 at 16:52
Possible duplicate of [Bug on Worksheet Change](https://stackoverflow.com/a/50868969/6706419). #SelfPromotion — urdearboy, Aug 22 '18 at 16:59

score 0 · Accepted Answer · answered Aug 22 '18 at 17:13

Try the code below, explanations inside the code's notes:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

' make sure the cell changed is "B7"
If Not Application.Intersect(Target, Range("B7")) Is Nothing Then
    If Target.Value2 = "Pass" Then
        Range("B8:B9").ClearContents
        Rows("8:9").EntireRow.Hidden = True
    Else
        Rows("8:9").EntireRow.Hidden = False
    End If
End If

Application.EnableEvents = True

End Sub

Clear Cell Content

1 Answers1