CLEARCONTENTS OF CELLS WHEN OTHER CELL VALUE IS DELETED

[JohnLute]

I've been struggling with this for hours and can't find any examples in any knowledge bases that have been helpful. I hope someone here can help!

What I'd like to do is clearcontents of Range("L30:T30") if the value in Range("C29") is DELETED. C29 is a data validation list. Whenever I delete the value in C29 nothing happens however I tested by expanding the validation list to include a Null value. When I select the Null value then the VBA code fires and clears contents as desired.

I don't want the Null value in the list. How can I modify the code to fire when the value in C29 is DELETED?


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Division As String
Division = Range("C29:E29").Cells(1).Value

If Target.Address = Range("C29").Address Then
If Not Intersect(Target, Range("C29")) Is Nothing Then
Range("L30:T30").ClearContents
If Division = "Foodservice" Then
MsgBox "Please enter a Salesperson!"
Range("L30:T30").Select
ElseIf Division = "Industrial" Or Division = "Retail" Then
Range("L30:T30").ClearContents
End If
End If
End If

End Sub

[Logit]

.
From what I see this line actually says 'If C29 is NOT empty Then'


If Not Intersect(Target, Range("C29")) Is Nothing Then

Try changing it to :


If Intersect(Target, Range("C29")) Is Nothing Then

or possibly :


If Intersect(Target, Range("C29")) = 0 Then

or :


If Intersect(Target, Range("C29")) = "" Then

[JohnLute]

Thanks for the reply. I missed the "NOT, thanks! I just tried everything you posted and nothing worked. The only time it fires is when I select Null in the data list. Totally annoyed!


.
From what I see this line actually says 'If C29 is NOT empty Then'


If Not Intersect(Target, Range("C29")) Is Nothing Then

Try changing it to :


If Intersect(Target, Range("C29")) Is Nothing Then

or possibly :


If Intersect(Target, Range("C29")) = 0 Then

or :


If Intersect(Target, Range("C29")) = "" Then

[p45cal]

.
From what I see this line actually says 'If C29 is NOT empty Then'


If Not Intersect(Target, Range("C29")) Is Nothing Then
Not quite, the Intersect method returns a range (it doesn't look at the values of the cells at all), and if none of the arguments intersect each other then it returns Nothing. So it's a way of checking whether what's been changed on the worksheet (Target), intersects at all with cell C29.
If Nothing is returned then cell C29 hasn't been changed, if something is returned (ie Not Nothing) then C29 is among the cells that have been changed, so do something about it.




What I'd like to do is clearcontents of Range("L30:T30") if the value in Range("C29") is DELETED

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Division As String

If Not Intersect(Target, Range("C29")) Is Nothing Then
Division = Range("C29").Value
If Division = "" Then Range("L30:T30").ClearContents
End If
End Sub
This is not quite the same thing, it is clearcontents of L30:T30 if cell C29 is amongst those cells changed and it is empty. The tiny difference is that if cell C29 was already empty, the L30:T30 cells will still be cleared.

[JohnLute]

Hmmm . . . I just tested ONLY the Not and ClearContents works when I select Null in the data list. If I remove Not and select Null in the data list then nothing happens. It appears that Not is needed.

If Target.Address = Range("C29").Address Then
'If Division = "Foodservice" Then
'MsgBox "Please enter a Salesperson!"
'Range("L30:T30").Select
'ElseIf Division = "Industrial" Or Division = "Retail" Then
'Range("L30:T30").ClearContents
'End If
'End If

If Not Intersect(Target, Range("C29")) Is Nothing Then
Range("L30:T30").ClearContents
End If
End If

[JohnLute]

Thanks, Mark! That did the trick!!! I appreciate your help in saving what's left of my sanity! :banghead:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Division As String
If Not Intersect(Target, Range("C29")) Is Nothing Then
Division = Range("C29").Value
If Division = "" Then Range("L30:T30").ClearContents
If Division = "Foodservice" Then
MsgBox "Please enter a Salesperson!"
Range("L30:T30").Select
ElseIf Division = "Industrial" Or Division = "Retail" Then
Range("L30:T30").ClearContents
End If
End If

[p45cal]

With reference to msg#5: That message is only a few minutes after I posted mine so I don't think you had a chance to see my message.
The lines:
If Target.Address = Range("C29").Address Then
and
If Not Intersect(Target, Range("C29")) Is Nothing Then
are doing almost the same thing. You don't need both.

If Target.Address = Range("C29").Address
checks if you have changed the cell C29 only

If Not Intersect(Target, Range("C29")) Is Nothing
checks whether the cell C29 is amongst those cells changed (you may have copy/pasted a range, or have had several cells selected and held the Control key down while pressing Enter to edit all of them at once).

Neither of the lines above checks the contents of cell C29, so regardless of what you enter or delete in cell C29, the range L30:T30 will be cleared.

[Logit]

.
Here is another method without using the Intersect(Target ....



Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Division As String
Division = Range("C29:E29").Cells(1).Value


If Me.Range("C5") = "" Then
Range("L1:T5").ClearContents

If Division = "Foodservice" Then
MsgBox "Please enter a Salesperson!"
Range("L30:T30").Select
ElseIf Division = "Industrial" Or Division = "Retail" Then
Range("L30:T30").ClearContents
End If

End If


End Sub

[JohnLute]

Thank you for the follow-up and clear explanation! This is very helpful!

[JohnLute]

Thank you, Logit! I definitely had a bit of a mess!


.
Here is another method without using the Intersect(Target ....



Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Division As String
Division = Range("C29:E29").Cells(1).Value


If Me.Range("C5") = "" Then
Range("L1:T5").ClearContents

If Division = "Foodservice" Then
MsgBox "Please enter a Salesperson!"
Range("L30:T30").Select
ElseIf Division = "Industrial" Or Division = "Retail" Then
Range("L30:T30").ClearContents
End If

End If


End Sub

[Logit]

.
You are welcome.