Clear cells automatically

[bigmaas]

E16 is a manually input date. F16:H16 is manually input text. I16 is manually
input number. When I clear E16 manually, I want to have F16:I16 cleared
automatically.
Can anyone help? Thanks

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "F16:I16"
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$E$16" And Target.Value = "" Then
Me.Range(WS_RANGE).ClearContents
End If
stoppit:
Application.EnableEvents = True
End Sub

This is worksheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP

 

[Fred Smith]

One option:
Highlight cells e16:i16, then press delete.

Regards,
Fred

 

[bigmaas]

Thank you for your reply. How would I expand this code to include the same
events for E17 thru E22? Many thanks
Regards
bigmaas

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Const WS_RANGE As String = "E17:E22"
Set rng = Target.Offset(1, 1).Resize(1, 4)
On Error GoTo stoppit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
rng.ClearContents
End If
stoppit:
Application.EnableEvents = True
End Sub


Gord

 

[Nicholas]

I have a similar issue. Help would be greatly appreciated.

I have a value in C3, and if it changes, I would like to have the contents
in D3:E22 cleared.

Thanks

 

[Don Guillett]

if target.address=range("c3").address then
range("d3:e22").clearcontents
end if

 

[Gord Dibben]

Simply change the Target cell and the range.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "D3:E22"
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$C$3" And Target.Value = "" Then
Me.Range(WS_RANGE).ClearContents
End If
stoppit:
Application.EnableEvents = True
End Sub


Gord