Dynamic SelectionChange based on current active row-CodePudding

I created a worksheet for myself containing multiple dependant dropdown lists. Basicly I want a range to be cleared when a change happens within a range. Therefor I wrote the code below, which works as intented but it is far from neat.

This is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("D13").Address Then
Range("D13:H13").Value = ""
ElseIf Target.Address = Range("D14").Address Then
Range("D14:H14").Value = ""
ElseIf Target.Address = Range("D15").Address Then
Range("D15:H15").Value = ""
ElseIf Target.Address = Range("D16").Address Then
Range("D16:H16").Value = ""
ElseIf Target.Address = Range("D17").Address Then
Range("D17:H17").Value = ""
ElseIf Target.Address = Range("D18").Address Then
Range("D18:H18").Value = ""
ElseIf Target.Address = Range("D19").Address Then
Range("D19:H19").Value = ""
ElseIf Target.Address = Range("D20").Address Then
Range("D20:H20").Value = ""
ElseIf Target.Address = Range("D21").Address Then
Range("D21:H21").Value = ""
ElseIf Target.Address = Range("D22").Address Then
Range("D22:H22").Value = ""
ElseIf Target.Address = Range("D23").Address Then
Range("D23:H23").Value = ""
ElseIf Target.Address = Range("D24").Address Then
Range("D24:H24").Value = ""
ElseIf Target.Address = Range("D25").Address Then
Range("D25:H25").Value = ""
ElseIf Target.Address = Range("D26").Address Then
Range("D26:H26").Value = ""
ElseIf Target.Address = Range("D27").Address Then
Range("D27:H27").Value = ""
ElseIf Target.Address = Range("D28").Address Then
Range("D28:H28").Value = ""
ElseIf Target.Address = Range("D29").Address Then
Range("D29:H29").Value = ""
End If
End Sub

Now what I would like to achieve is something like this:

If Target.Address = Range("D13:D29").Address Then
Range("D Target.Address.Row : H Target.Address.Row").Value = ""
End If
End Sub

If someone can put me in the right direction of how to use the Target.Address within a range selection or in a better suitable solution/option i`d be very thanksfull :)

CodePudding user response：

You can use a loop and Resize so you have not to repeat the same code over and over:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim AffectedRange As Range  'check wich target cells are affected
    Set AffectedRange = Intersect(Target, Me.Range("D13:D29"))

    If Not AffectedRange Is Nothing Then
        Dim Cell As Range
        For Each Cell in AffectedRange ' clear range for each affected cell
            Cell.Resize(ColumnSize:=5).Value = vbNullString
        Next Cell
    End If
End Sub

CodePudding user response：

The code could be simplified to this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("D13:D29")) Is Nothing Then
        Target.Resize(, 5).Value = ""
    End If
   
End Sub