CODEWITHSUNDEEP
excelvba
Kelsey
Kelsey

Reputation: 11

Use VBA to clear contents based on an update to dynamic dropdown

I am trying to clear the contents of a cell based on an update to another cell using VBA.

Columns E through H are all dynamic dropdown lists. I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D2")) Is Nothing Then
        Range("E2:H2").ClearContents
    End If
End Sub

This works on Row 2, but how do I make it a range so that if I change D3, it will clear E3:H3 and so on for all of the rows. Also, can I make it that if I change anything in D through H, it will clear out the entire row?

Thank you!

Upvotes: 1

Views: 262

Answers (2)

Patouf
Patouf

Reputation: 46

Maybe by using the target's row and concatenate the clear content range with it, like this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D" & Target.Row)) Is Nothing Then
        Range("E" & Target.Row & ":H" & Target.Row).ClearContents
    End If
End Sub

A very quick way to do if from your comment ask:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D" & Target.Row)) Is Nothing Then
        Range("E" & Target.Row & ":H" & Target.Row).ClearContents
        
        ElseIf Not Intersect(Target, Range("E" & Target.Row)) Is Nothing Then
            Range("F" & Target.Row & ":H" & Target.Row).ClearContents
            
            ElseIf Not Intersect(Target, Range("F" & Target.Row)) Is Nothing Then
            Range("G" & Target.Row & ":H" & Target.Row).ClearContents
            
            End If
       End If
    End If
End Sub

Upvotes: 2

FunThomas
FunThomas

Reputation: 29466

Assuming that you don't want to clear your header row

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 then exit sub

    ' This will clear the row if something is changed in Column D:
    If Not Intersect(Target, Range("D:D")) Is Nothing Then
        Range(Cells(Target.Row, "E"), Cells(Target.Row, "H")).ClearContents
    End If
End Sub

If you want to clear the entire Row, you could use

Target.EntireRow.ClearContents

However, that leads to 2 problems:
You would clear also the information that was just entered (because it's part of the row). You could prevent that by saving the data:

Dim backup As Variant
backup = Target.value
Target.EntireRow.ClearContents
Target.value = backup

Second problem is that writing something in a cell using VBA will again trigger the Change-Trigger (and so on, eventually causing a Stack Overflow runtime error). To avoid that, you need to disable Events while the trigger code is running - and you must not forget to enable them at the end.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Goto Change_Exit  ' To ensure that events are reactivated in any case
    Application.EnableEvents = False
    (do your stuff here)

Change_Exit: 
    Application.EnableEvents = True
End Sub

Update: To clear only the fields to the right, use

If Not Intersect(Target, Range("D:G")) Is Nothing Then
    Range(Target.Offset(0, 1), Cells(Target.Row, "H")).ClearContents
End If

Upvotes: 2

Related Questions