-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Automatically clearing the contens of cell's.
- Thread starter GI30065
- Start date
chicagodiceman
Active Member
- Joined
- Mar 13, 2004
- Messages
- 371
This will do just that:
But there are some problems with this solution. You must populate B3 before you can populate any cell in range E3:M3 because as long as the cell B3 is empty, the other contents are removed.
If you are just going to cell B3 and hitting the delete button to clear that cell, you could refine it a bit so that this only fires when the activecell is B3 as follows:
How are you deleting the text in B3? Will it be specific to only these cells?
If you could give a slightly better description of what you're trying to achieve, you may get a better solution.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("B3").Value = "" Then
Range("E3:M3").ClearContents
Else:
End
End If
End SubBut there are some problems with this solution. You must populate B3 before you can populate any cell in range E3:M3 because as long as the cell B3 is empty, the other contents are removed.
If you are just going to cell B3 and hitting the delete button to clear that cell, you could refine it a bit so that this only fires when the activecell is B3 as follows:
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If ActiveCell = Range("b3") And Range("B3").Value = "" Then
Range("E3:M3").ClearContents
Else:
End
End If
End SubHow are you deleting the text in B3? Will it be specific to only these cells?
If you could give a slightly better description of what you're trying to achieve, you may get a better solution.
Upvote
0
THANK YOU Sooo MUCH
Both scripts do exactly the thing I was looking for.
Although I don't understand the difference between the two.
I need this for B3 -- E3_M3
B4 -- E4_M4 ,...
Until B52 -- E52_M52
Tried to edit the script, but it doesn't work, I'll guess I'm a real dummie.
Best Regards,
Marc.
Both scripts do exactly the thing I was looking for.
Although I don't understand the difference between the two.
I need this for B3 -- E3_M3
B4 -- E4_M4 ,...
Until B52 -- E52_M52
Tried to edit the script, but it doesn't work, I'll guess I'm a real dummie.
Best Regards,
Marc.
Upvote
0
chicagodiceman
Active Member
- Joined
- Mar 13, 2004
- Messages
- 371
Actually, the difference is the second version allows you to enter data into the E-M range without it clearing while B is empty. By running the code only when in column B, you don't accidentally enter info in the other columns just to have it disappear when you change cells.
Not a dummy, I had to go back in and edit my response after I realized that it would do that.
This should do the trick. It checks to see if you are in column B (2) then if you delete the contents of the active cell, it clears E-M.
Not a dummy, I had to go back in and edit my response after I realized that it would do that.
This should do the trick. It checks to see if you are in column B (2) then if you delete the contents of the active cell, it clears E-M.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If ActiveCell.Column = 2 And ActiveCell.Value = "" Then
Range(ActiveCell.Offset(0, 3), ActiveCell.Offset(0, 11)).ClearContents
Else:
End
End If
End Sub
Upvote
0
Thanks for all the effort you made !!!
It's nice to know that there are still people who want to help others.
The very first script is fine for me, since I have to delete the data anyway.
The thing I want is the first script but for these cells ==>
B3 -- E3=>M3, B4 -- E4=>M4 ,... Until B52 -- E52=>M52
Does this then means I'll have to write the script 52 times ?
Very Best Regards,
Marc.
It's nice to know that there are still people who want to help others.
The very first script is fine for me, since I have to delete the data anyway.
The thing I want is the first script but for these cells ==>
B3 -- E3=>M3, B4 -- E4=>M4 ,... Until B52 -- E52=>M52
Does this then means I'll have to write the script 52 times ?
Very Best Regards,
Marc.
Upvote
0
mdmackillop
Board Regular
- Joined
- Aug 24, 2006
- Messages
- 53
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
On Error GoTo ErrH
If Target.Columns.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("B3:B52")) Is Nothing Then
For Each Cel In Target
If Cel = "" Then
Cel.Offset(, 3).Resize(1, 9).ClearContents
End If
Next
End If
ErrH:
Application.EnableEvents = True
End Sub
Upvote
0
Similar threads
- Question
- Solved
- Question
