Clear cells automatically

  • Thread starter Thread starter bigmaas
  • Start date Start date
B

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
 
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
 
Thank you for your reply. How would I expand this code to include the same
events for E17 thru E22? Many thanks
Regards
bigmaas
 
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
 
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
 
if target.address=range("c3").address then
range("d3:e22").clearcontents
end if
 
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
 
Back
Top