Delete cell contents when user deletes adjacent cell contents 1

[LScharf]

Hi, This should be pretty simple: I want to create a macro that deletes the contents of B19 and C19 as soon as the user deletes the contents of A19.

I started with this but it doesn't work:

Option Compare Text
Sub clear_stuff()
If Range("A19").Value = "" Then
Range("B19:C19").ClearContents
End If
End Sub

Thank you.

 

[SkipVought]

Hi,

Just A19 or any cell in column A, or any cell in column A row 2 or greater?

Here's for what you asked in the WORKSHEET CODE SHEET (right-click sheet tab, select View Code...

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Count > 1 Then Exit Sub

     If Not Intersect(Target, Range("A19")) Is Nothing Then
        If Target.Value = "" Then Range(Target.Offset(0, 1), Target.Offset(0, 2)).ClearContents
     End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LScharf]

Thanks! That does the trick. I added to it to cover a range of cells:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Count > 1 Then Exit Sub

For Each i In Range("A19:A25")
     If Not Intersect(Target, i) Is Nothing Then
        If Target.Value = "" Then Range(Target.Offset(0, 1), Target.Offset(0, 2)).ClearContents
     End If
Next i
End Sub

 

[SkipVought]

No need for a loop...

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Count > 1 Then Exit Sub

     If Not Intersect(Target, Range("A19:A25")) Is Nothing Then
        If Target.Value = "" Then Range(Target.Offset(0, 1), Target.Offset(0, 2)).ClearContents
     End If

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LScharf]

Beauty...

 

[LScharf]

Although it actually doesn't work without the loop...

 

[SkipVought]

Sure works for me, exactly as I posted!

Explain EXACTLY what is not working!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LScharf]

It's not working in that I delete the value from cell A19 (or A20, 21, etc.) and the adjacent cells (B19, C19) stay unchanged.

FYI, I am using Excel 2010.

 

[SkipVought]

You select A19 ONLY

Hit the [DELETE] key

...and NOTHING happens?

Do you have macros enabled?

Did you COPY 'n' PASTE the code AS POSTED above without change?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...too much burbon in your Pure Vermont Maple Syrup? ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LScharf]

The bourbon appears to have worn off... ) Today the macro works. And for the benefit of others looking at this thread, I was able to use a named range:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Count > 1 Then Exit Sub

     If Not Intersect(Target, Range("Clearer")) Is Nothing Then
        If Target.Value = "" Then Range(Target.Offset(0, 1), Target.Offset(0, 2)).ClearContents
     End If

End Sub

Thank you, Skip!

 

[LScharf]

Further guidance requested... I need to clear the contents of several columns, and they're not adjacent.

This clearly doesn't work:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Count > 1 Then Exit Sub

     If Not Intersect(Target, Range("Clearer")) Is Nothing Then
        If Target.Value = "" Then Range(Target.Offset(0, 1), Target.Offset(0, 2)).ClearContents
     End If
     
     If Not Intersect(Target, Range("Clearer2")) Is Nothing Then
        If Target.Value = "" Then Target.Offset(0, 1).ClearContents and Range(Target.Offset(0, 6), Target.Offset(0, 7)).ClearContents '"and" doesn't work
     End If

End Sub

 

[SkipVought]

If you are specificly refering to the commented code ...

     If Not Intersect(Target, Range("Clearer2")) Is Nothing Then
        If Target.Value = "" Then 
           Target.Offset(0, 1).ClearContents
           Range(Target.Offset(0, 6), Target.Offset(0, 7)).ClearContents '"and" doesn't work
     End If

Note that the AND Operator is a, well ah, er, uh, an OPERATOR



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LScharf]

Perfecto! Thanks a million. You are a smooth... operator.

Final code with necessary End Ifs:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Count > 1 Then Exit Sub

     If Not Intersect(Target, Range("Clearer")) Is Nothing Then
        If Target.Value = "" Then Range(Target.Offset(0, 1), Target.Offset(0, 2)).ClearContents
     End If

     If Not Intersect(Target, Range("Clearer2")) Is Nothing Then
        If Target.Value = "" Then
           Target.Offset(0, 1).ClearContents
           Range(Target.Offset(0, 6), Target.Offset(0, 7)).ClearContents
        End If
     End If
End Sub