Clearing cells if a dropdown selection changes

[Robzx]

Hi all

I'm trying to write some code that will automatically clear the contents of two cells, E3 and F3 when the dropdown selection in column C3 changes, and for a range of rows 3 to 17.

The code that I have is

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

    For Each Cell In Target
        If Cell.Address = "$C$3:$C$17" Then
            Application.EnableEvents = False
                Range("$E$3:$F$17").ClearContents
            Application.EnableEvents = True
        End If
    Next Cell

End Sub

But I can't quite get it to work, what have I missed?

Thanks

 

[Michael M]

maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$C$3:$C$17")) Is Nothing Then Exit Sub
Application.EnableEvents = False
    Range("$E$3:$F$17").ClearContents
Application.EnableEvents = True
End Sub

 

[Robzx]

Thank you Michael, but when one cell selection is changed, the entire range is cleared. I'm trying to only clear E3 and F3, when C3 changes, but any cell in the range of C3:C17 could be changed.

 

[Michael M]

Sorry, I must have misunderstood your request...try

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$C$3:$C$17")) Is Nothing Then Exit Sub
Application.EnableEvents = False
    Target.Offset(0, 2).ClearContents
    Target.Offset(0, 3).ClearContents
Application.EnableEvents = True
End Sub

 

[Peter_SSs]

Sorry, I must have misunderstood your request...try

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$C$3:$C$17")) Is Nothing Then Exit Sub
Application.EnableEvents = False
    Target.Offset(0, 2).ClearContents
    Target.Offset(0, 3).ClearContents
Application.EnableEvents = True
End Sub

Michael, I think that needs some more careful consideration.
With data in all columns up to column J, try selecting B3:C4 or C6:F10 and pressing the Delete key.

My suggestion is:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range

  Set Changed = Intersect(Target, Range("C3:C17"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    Changed.Offset(0, 2).ClearContents
    Changed.Offset(0, 3).ClearContents
    Application.EnableEvents = True
  End If
End Sub

 

[Robzx]

Gents, thank you very much for the help. It looks as though Peter's suggestion is the most efficient for my needs. If it is a larger range of cells that needs clearing would it be

    Application.EnableEvents = False
    Changed.Offset(0, 2).ClearContents
    Changed.Offset(0, 3).ClearContents
    Changed.Offset(0, 4).ClearContents
    Changed.Offset(0, 5).ClearContents
    Changed.Offset(0, 6).ClearContents
    Changed.Offset(0, 7).ClearContents

and so on until the nth column is reached or could the upper limit be specified?

Thank you once again for your help

 

[Peter_SSs]

Depends on just what cells you want to clear, but here is one way to do a larger set of columns.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range

  Set Changed = Intersect(Target, Range("C3:C17"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    Intersect(Changed.EntireRow, Columns("E:K")).ClearContents
    Application.EnableEvents = True
  End If
End Sub

 

[Robzx]

Thank you Peter, but I'll stick with the offset approach

 

[Peter_SSs]

Thank you Peter, but I'll stick with the offset approach

OK, your choice.

 

[Michael M]

you could use a loop to do it

for x = 2 to 7
Changed.Offset(0, x).ClearContents
next x

BUT Peters code is far more efficient !!!