Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Clear cells automatically
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 4
Default Clear cells automatically
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default Clear cells automatically
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


On Sat, 15 Aug 2009 06:39:01 -0700, bigmaas
wrote:

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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,389
Default Clear cells automatically
One option:
Highlight cells e16:i16, then press delete.

Regards,
Fred

"bigmaas" wrote in message
...
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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 4
Default Clear cells automatically
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" wrote:

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


On Sat, 15 Aug 2009 06:39:01 -0700, bigmaas
wrote:

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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default Clear cells automatically
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

On Sun, 30 Aug 2009 03:05:03 -0700, bigmaas
wrote:

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" wrote:

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


On Sat, 15 Aug 2009 06:39:01 -0700, bigmaas
wrote:

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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 19
Default Clear cells automatically
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

"Gord Dibben" wrote:

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

On Sun, 30 Aug 2009 03:05:03 -0700, bigmaas
wrote:

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" wrote:

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


On Sat, 15 Aug 2009 06:39:01 -0700, bigmaas
wrote:

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




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,522
Default Clear cells automatically
if target.address=range("c3").address then
range("d3:e22").clearcontents
end if

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nicholas" wrote in message
...
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

"Gord Dibben" wrote:

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

On Sun, 30 Aug 2009 03:05:03 -0700, bigmaas
wrote:

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" wrote:

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


On Sat, 15 Aug 2009 06:39:01 -0700, bigmaas
wrote:

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





Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default Clear cells automatically
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

On Wed, 3 Mar 2010 06:43:01 -0800, Nicholas
wrote:

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

"Gord Dibben" wrote:

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

On Sun, 30 Aug 2009 03:05:03 -0700, bigmaas
wrote:

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" wrote:

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


On Sat, 15 Aug 2009 06:39:01 -0700, bigmaas
wrote:

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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
clear the clear the web page email attachment lines MCrider Excel Discussion (Misc queries) 0 November 11th 07 10:05 PM
Cells.Clear Ben Dummar Excel Discussion (Misc queries) 3 January 24th 07 10:50 PM
Clear Cells Mike Excel Discussion (Misc queries) 4 November 20th 05 11:50 PM
Automatically clear values from a range of selected cells John Davies Excel Discussion (Misc queries) 1 June 28th 05 04:42 PM
How do I clear data in selected cells automatically Jim Train Excel Discussion (Misc queries) 1 May 25th 05 06:45 PM


All times are GMT +1. The time now is 10:22 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement